Whichever version you use, to split a database is simple enough to apply and set up using the normal method of the Database Splitter tool which is a built in utility separating your tables from all the other remaining objects known as the front-end Microsoft Access Database.
However, to manually split an Access database means learning how to create linked tables to your front-end database which typically stores all your queries, forms and reports.
Just as a reminder for
those not too sure of the benefits of splitting a database, you may want to take a quick look at this article called learn about the benefits of a split Access database.
Split A Database: How To Manually Split A Microsoft Access Database – The Steps
I’m going to use Access 2010 as the version here to show you the key steps but this will apply to all versions including the latest Access 2013 release.
1. Create a new default blank Access database (and close the default new table).
2. In the main environment, go to the External Data tab; click Access in the Import & Link section.
3. In the Get External Data dialog box, click on the Browse button to locate and select the database that you want to split, click to select the Import tables, queries, forms, reports, macros, and modules into the current database option (first one), and then click OK.
4. In the Import Objects dialog box, click the Select All button (or choose a selection of tables to split a database) from the Tables tab, and then click OK.
This will now import all the Access tables into the new database, which will become your back-end database.
5. Store the new back-end database on a network shared folder, and make sure that all the users have full permissions to the share this location too!
6. Create a second new default blank Access database.
7. In the External Data, click Access in the Import & Link section.
8. In the Get External Data dialog box, click on the Browse button to locate the back-end database that you had just created, click to select the Link to the data source by creating a linked table option (second one), and then click OK.
9. In the Import Objects dialog box, click the Select All button on the Tables tab, and then click OK.
Notice that Microsoft Access links all the tables in the back-end database to your newly created front-end database.
10. On the External Data, click Access in the Import & Link section.
11. In the Get External Data dialog box, click the Browse button to select the original database that you are splitting, click to select the Import tables, queries, forms, reports, macros, and modules into the current database option, and then click OK.
12. In the Import Objects dialog box, click the Select All button starting on the Forms tab, and repeat this step on all other tabs except the Tables tab because you have already linked to the tables. Remember, you only need to import the rest of the objects, and then click OK.
Notice that you now have all the tables linked (from the back-end database) and have imported all the remaining objects (to the front-end database).
I normally add a ‘_be’ prefix to denote a back-end database so it becomes clear when storing this database file in a shared folder location. The front-end database can be distributed to each user’s local desktop and will require no further maintenance.
To really tighten your database down, you can add security to it too whether be the default database encrypted password feature of perhaps building your own custom user login security utility.
Either way, to manually split a database in Access gives you a better understanding and control of all functions the administrative database use will require.
Tags: database splitter tool, how to split an access database, linked tables in access, microsoft access database, share access database, split a database