Microsoft Access is unique among all of the Microsoft Office applications in that it can be used to create entire applications that contain large storehouses of data, versatile data entry forms and sophisticated reports to present the data in a variety of ways. It also goes beyond other applications such as Microsoft Excel in that a single database is able to store and organize large amounts of data from different sources and on different subjects while enabling many users to easily find and work with that data as needed. While an Excel spreadsheet is excellent for analysis and can provide access to multiple users, Access takes it a few steps further and enables users to store and manage volumes of data and present it in many different ways based on the needs of the data users and the intended distribution.

Why Split a Database?

The multi-user environment provides its own challenges. Access is often used as an ad hoc development tool by people outside the I.T. department to design needed solutions that would otherwise have to be purchased for large amounts of money. These applications can end up being used by many people across a company and that means there can be many people accessing the same database file at the same time. If enough people are in the file at once, the performance of the application can degrade pretty quickly. Also, when one person has the database file open, the file is then read-only for anyone else who opens it which means that they will be unable to create or edit their own reports or database queries.

Fortunately, Access has another unique feature and that is the ability to split databases into front-end and back-end files:

• The back-end file contains the data tables and relationships only and can be placed on a network drive that all users have access to.
• The front-end file contains the rest of the database objects including data-entry forms, reports, queries and any custom programming. It also contains a link to each table in the back-end file through which it accesses the data just as it would if it contained the tables themselves. Many copies of the front-end file can exist and can be placed in any convenient directory so long as the user has access to the directory containing the back-end file.

To make a little clearer, the following diagram shows an example of this arrangement.

Under this arrangement, each user has their own copy of the front-end file which contains all the user interface objects such as forms and reports but each copy is accessing the same data tables.

• This puts much less demand on the shared portion of the database and lets each user customize their own forms and reports as needed.
• Splitting a database also reduces network traffic since only the table data is being sent across the network.
• The risk of database corruption is reduced as well because you don’t have everyone using the same file for everything they need.
• If there’s a central person doing all the development, then that person can make changes to the front-end and distribute it to the other users.
• As indicated in the diagram, multiple versions of the front-end can also be maintained to grant different types of access to different users. For example, the front-end that’s given to the warehouse doesn’t even need links to tables that the employees there don’t use such as the payroll information tables.

Creating the Front-end and Back-end

The best time to split a database is when you first create the application; you create the tables in one file and then create another file with the forms, reports and links to those tables. Splitting a pre-existing application is just as easy.

First, be sure to make a backup of your original database. When you’re moving files around, accidents can happen so be sure to make a backup in a safe place.

To split an existing database:

1. Under the Database Tools tab of the Access ribbon, click on Access Database under the Move Data group. This will start the Splitter Wizard.
2. Click the Split Database button and specify a location for the new back-end file. Then click Split.

That’s all it takes. Access will automatically create a new database file, move your data tables into it and create links to them in the original file.  Below, you can see an example of the two files side-by-side.

To create new table links:

If you’re creating a new Access application and want to split it into two files from the start, you can create both files and the links yourself.

1. Create the back-end file and give it a name that identifies it as such.
2. Design the data tables in the back-end file and create any necessary relationships between them.
3. Create the front-end file in which you’ll be designing the forms and reports.
4. In the front-end file, select External Data from the Access ribbon and then select Access under the Import & Link section.
5. In the Get External Data wizard, use the Browse button to select the back-end file you created, select Link to the data source by creating a linked table and click OK.
6. The Link Table dialog will appear. Select the tables that you want to link to and then click OK.

This will create the links in the front-end file. You can also link to tables in more than one back-end file if you want to combine data from multiple Access databases.

Using the Linked Table Manager

Once you create the links to the back-end database, you can refresh or change them using the Linked Table Manager from the External Data tab on the Access ribbon. This might be necessary if you change the location of the back-end database file or if you’re the primary database developer and you’re keeping a separate copy of the back-end in a test environment.

The important thing to notice about the Linked Table Manager is that it maintains absolute paths to the back-end file. This means that the front-end file can be moved to any machine on the network and it will still maintain the link to the back-end so long as the user has access to that location. In the screenshot, I’m using a location with a mapped drive letter, but you can also specify an unmapped network location, i.e.

\\SERVER-MAIN\AccessDatabases\OrderProcessing_be.accdb

This might be best if not all users on your network have the same drive mappings. As an added bit of security, you can also place the back-end file in a hidden directory and link to it there if you want to be sure your users can’t get to it.

So long as all the links that you select have the same address to start with, you should only have to specify the new address once. If even one of the links has a different address, however, the manager will ask you to specify an address for each an every link selected.

Finally, the Linked Table Manager will only show the table links that are visible in the Database Window. If you have an table links with the Hidden attribute set, you will need to make them visible in the Database Window before you can work with them in the Linked Table Manager.

Multi-Tier Design

Dividing an application into separate layers as shown here, with the user interface in one layer and the data in the other, is an example of multi-tier design. Typical Access database design, where everything sits in one file is called single-tier design but it’s not the norm in professional development.  If you move beyond Access to use more advanced tools such as SQL Server for the database and Microsoft Visual Studio for the interface design, you’ll see that two- and three-tier design of applications is the standard. Adopting the best practices early will help you later one as you need to use new technologies.

While you’re here, check out some of the other articles on Microsoft Access right here on ComeauSoftware.com.

For More Information ….

You can find more information in the Split an Access Database article on the Office Support site. Tech Republic also has more information on all the reasons to split an Access database if you’re still not convinced of the need.