Splitting a Microsoft Access Database

In this article, I will mention about the pros and cons of splitting the database, and implementing how to split a database step by step.

Generally, you will split your MS Access database into front-end database and back-end database, which back-end application will contains only the database table and front-end application will contain all of the queries, forms, reports, macros and modules. In a multi-user environment, the front-end database will be distributed to each client machine, and the back-end database will be installed on the server.

There are several advantages to split a database:
• Multiple users across a network share one common set of data
• Multiple users can update data at the same time
• Users will not be able to make design changes to tables
• You can update the application (forms, reports etc) without interrupting processing or corrupting the data

The disadvantages to split a database:
• All authorized users must have read/write permissions on the back-end database, though security measures must be implemented
• When a large number of users access to the back-end database at the same time, the network and database must be able to handle the traffic.

To run a single copy in a network directory which is shared by multiple users is likely to result in:
• Excessive network traffic
• Slow response
• Possibly file corruptions

How to Split a Microsoft Access Database
Splitting a database into front-end and back-end database is very easy seems it provides Database Splitter Utility already.
1. Open the database to be split
2. From the main menu, choose Tools | Database Utilities | Database Splitter
3. Click on Split Database
4. Enter a name for the back-end database. Default file name will appear as DbName_be.mdb.
5. Click the Split button
6. Click OK when the message appears informed that database split successfully
7. Test on both databases to make sure that proper links have been created.

Now, when you click on the tables tab of your front-end database, you can see that all the table names have an arrow next to them. This is an indicator that they are linked to data outside of this file.

Maintaining a Split Database
After the database is split, both of the database file must be keep in sync. If the back-end database is changed or moved, you must refresh the linked tables. There is 2 ways to update the linked tables in a split database.

Linked Table Manager
If you have added new fields to the existing table or remove the back-end database to a new location, then you can use Linked Table Manager to update the linked tables. To update the linked tables:
1. From the main menu, choose Tools | Database Utilities | Linked Table Manager
2. Check the Always prompt for new location check box.
3. Choose Select All.
4. Click OK.
5. A dialog window will prompt out. Select the location of your back-end database.
6. Click OPEN.
7. A message will indicate that all selected linked tables were successfully refreshed.

If you have added new tables into your back-end database, there is another way to update it into your front-end database. To update it:
1. From the main menu, choose File | Get External Data | Link Tables.
2. Navigate to your updated back-end database and click Link.
3. A window will prompt out with the list of tables name in your back-end database. Select the new table that you have updated and click OK.
4. The new updated table will be appeared in your front-end database.