It may sound like rocket science, but splitting an Access database is actually very straightforward and easy to do, and can lead to many benefits if you have multiple users sharing the same Access database. This article explains what happens when a database is split, summarises the benefits and then describes how to carry out a split.

An Access database can be shared by multiple users without the need to split the file. The database file could be stored in a shared folder accessible by all users and each could then open the file and work with the database. However in this situation all data, queries, forms and reports are sent backwards and forwards between each user and the database and this can result in relatively high data traffic rates.

If an Access database file is split, two files are created, one with the data tables and the other with all the other items such as queries, forms and reports. The data tables file is called the backend and the file with all the other items is called the frontend.

The backend contains only the data tables and is stored in a shared folder, perhaps on a network drive, which all users are allowed to access. The frontend contains all the forms, queries and reports and there's a copy of the frontend file stored on each user's computer.

When the user's frontend Access file is opened for the first time, an Access Link Manager is used to link the frontend file to the tables in the backend file. Then the user can use the frontend database just like the original. However this time only table data is sent backwards and forwards between each user and the backend because each user has all the queries, forms and reports on their own computer and this can result in smaller data traffic rates.

There are other benefits of splitting a database in addition to reduced data traffic. For example frontends can be tailored for different user needs, so some users may have different forms and reports compared to others. Or some users may only have access to particular tables, for example for data entry, whereas other users may have access to other tables for analysis purposes. Frontends can also be developed or changed over time and updated on users' computers without affecting the backend database. In addition, database security can be enhanced with a backend file stored on a secure server with additional security features.

How to create a split Access database

Before splitting a database it's essential that you create a backup copy as a safety measure. Then with the required database still open on your own computer carry out the split using the Database Splitter wizard. The wizard works in the same way for different Access versions but is enabled in different ways.

To launch the wizard in Access 2003 choose Tools, Database Utilities, Database Splitter. To launch the Database Splitter wizard in Access 2007/2010 choose the Database Tools tab and in the Move Data group click the Access Database button.

In the wizard click the Split Database button and in the "Create Backend Database" panel choose a filename for the backend database and a destination folder. Then click the Split button to make the split. Access will then split the current database into two files. You'll find that your current database file now has linked tables, linked to the original tables which are now in the backend file. All queries, forms and reports are still in your database and work exactly as before because they still use the same data sources in the current database, although these are now linked tables.

If you move or rename the backend database file, then open the frontend, the linked tables will fail. However Access has a Linked table manager which you can use to relink the tables to the moved or renamed file.

So splitting an Access database can have many advantages if you need to have an Access database shared by several users. Access has its own Database Splitter wizard which creates a separate backend database file on a specified location, often a shared folder and the original database used linked tables to link to the data. So for the user the database works just as normal but there is less data traffic, the frontend can be more easily changed or updated and the backend data can be kept more securely.

Interested in finding out more? You might like to have a look at some of the many instructor lead training courses available. The best ones are practical and hands on, and can really boost your Access skills.