Splitting an Access database

A simple Access system holds all its data, forms, queries, and reports in a single database. You will get better performance and security if you split these components, moving the data into a separate "backend" database and leaving the forms and reports in the "frontend"

Using the Database Splitter

You can separate the components by hand but Access provides the Database Splitter Wizard to automate the task. This creates a backend database named database_be, moves all the tables into this database, and links them to your original database which becomes the front end of the system.

You will find the Database Splitter under the Tools menu.

Advantages of splitting your database

• The back end database can have a password attached to keep casual users out of it.
• A user trying to write a new query or a new report in their copy of the front end database might sometimes lock, crash or corrupt their database. This has no effect on the data which is safely stored in the back end.
• Different versions of Access can share the same back end database. Access XP, 2000, and 97 front ends can all share the same Access 97 back end. This flexibility can save time and money when new PCs with new versions of Office are added to the network.
• Backup is simplified. The data in the back end can be backed up more frequently than the code in the front end.

Using a split database on a single PC

If you are working on a single PC then you will not see many benefits from splitting your database. The only one that really applies is the fact that you are free to experiment with the front end database without running the risk of losing any data.

One disadvantage that you will notice is that you will not be able to change the structure of the tables when you are working with the front end database. The tables are now in the separate back end database and although you can add, modify, and delete data from the front end, you can only change the structure of the tables from within the back end database itself.

Using a split database on a network

This is where the real advantages of a split database show. You can hold the back end database on your server and install a copy of the front end onto each of the user's PCs. Each user will have their own local copies of the forms and reports and these will run more quickly than when they were being retrieved from the server every time that they were needed. The only traffic on the network now will be the data itself.

Linking tables

The first time that you open the front end database on a new user's PC you will get an error message that Access cannot find the tables. This is because the front end has been moved. Either link the tables again by hand or use the Linked Table Manager. This will be on the Tools menu under Database Utilities or Add-Ins depending on the version of Access.

You can also link the tables automatically by using DAO techniques in program code.

Managing a database on a network

The back end database will be backed up as part of the regular server backup routine so your data will automatically be kept safe. The front end databases on each individual PC won't necessarily be being backed up so it's a good idea to keep a master copy of the front end on the server too. This is not for everyday use, it's just being kept on the server so that you will have a clean version of the front end that can be copied down to any new PC that needs to use the database.