Access 2016: Split a Database
Splitting a database involves separating the data from the rest of the database, and can help with performance when multiple users share the database over a network.
It's a good idea to split any database that needs to be shared by multiple users over a network. This can help with performance, and it can reduce the chance of the database file becoming corrupt due to multiple users accessing it at the same time.
Splitting a database involves separating the database into two files. One file is the back-end database that contains all the tables and data. The other file is the front-end database that contains all other objects, such as queries, reports, and forms.
Users work with a local copy of the front-end database, and the data is sent/retrieved from the back-end database. So, there could be many copies of the front-end database, but only one copy of the back-end.
By doing this, only the data needs to be sent across the network. With a non-split database, all database objects need to be sent across the network, which typically results in a slower experience for the user.
Also, users can create their own queries, forms, reports, macros, etc without impacting on the other users.
Split the Database
We'll now split the Music database that we've created throughout this tutorial.
Be sure to back up your database before you split it. Doing this will enable you to restore the database to its pre-split state in case you need to do so for whatever reason.
-
Launch the Database Splitter Wizard
Ensuring the Database Tools tab is open on the Ribbon, click the Access Database button (in the Move Data section).
This launches the Database Splitter wizard.
-
Initiate the Split
Click Split Database.
-
Choose a Location for the Data File
Choose a name and location for the data file and click Split.
It's a good idea to leave the name as Access suggests it (with a _be suffix.
The first part of the name is taken from the original database file (which will still be used by the front-end), and the _be suffix indicates that it's a back-end database.
Don't change the file type unless someone needs to use the file with an earlier version of Access that doesn't support the current file type.
-
Split Successful
The database will be split and a confirmation message will be displayed.
Click OK.
Check the Front-End Database
You can verify that the split took place by opening both database files.
First, let's open the original database. It uses the original database file. In our case, this is the Music.accdb file.
-
Tables
In the front-end database, in the left navigation pane, the table names will be prefixed with a small arrow. Also, if you hover over them, you will see the location of the data file. And if you try to open them in Design View, you'll be told that you can only do that from the back-end database.
-
Design View
In the front-end database, if you try to open a table in Design View, you'll be told that you can only make changes from the back-end database.
Check the Back-End Database
This is the database that holds the data. It uses the new database file that we created (Music_be.accdb).
-
Tables
You'll see that only the tables are displayed in the left navigation pane. None of the objects are displayed. That's because all the other objects are in the front-end database.
You can still open the table and view all data in Datasheet View.
-
Design View
In the back-end database, you can open the table in Design View and make updates to data types, properties, etc.
Now, if you've decided that you do in fact need to split your database into front-end and back-end, you will probably also want to do one more thing — turn the front-end into an executable file.