Microsoft Access Database Management

The purpose of this article is to propose a best practice solution for managing Microsoft Access Databases. We will cover best practices including splitting a database, and when to compact and repair.

Contents

  1. Overview
  2. Best Practices
    • A. Why Split a Database?
    • B. Benefits of Splitting a Database
    • C. What is the Front End Vs the Back End?
    • D. When to Compact and Repair
  3. How to split a database
  4. Linking New Tables
  5. Deleting Tables
  6. Other Resources

Best Practices

Why Split a Database?

Consider splitting any database that several people share over a network. Splitting a shared database can help improve its performance and reduce the chance of database file corruption. When you split a database, you reorganize it into two files — a back-end database that contains the data tables, and a front-end database that contains all the other database objects such as queries, forms, and reports. Each user interacts with the data by using a local copy of the front-end database.

Benefits of Splitting a Database

  • Improved reliability - If a user encounters a problem and the database closes unexpectedly, any database file corruption is usually limited to the copy of the front-end database that the user had open. Because the user only accesses data in the back-end database by using linked tables, the back-end database file is much less likely to become corrupted.

  • Greater availability - Because only the data is sent across the network, database transactions such as record edits are completed more quickly, which leaves the data more available to edit.

  • Improved performance - The performance of the database usually improves significantly because only the data is sent across the network. In a shared database that is not split, the database objects themselves — tables, queries, forms, reports, macros and modules — are sent across the network, not just the data.

What is the Front End vs. the Back End?

Splitting the database creates two files out of a single Access Database File. There is the original file, which becomes the Front End. We'll refer to this file as the Front End for this section of the document. The newly saved saved file, usually the original file name with a trailing _be for Back-End.

With two files instead of one, knowing which file to use can be confusing. The recommendation is to Copy the Front End to any users who work with the database and leave the original file in the same location. This allows the Front End to still be copied if a user's local copy is corrupted, deleted or if another user needs to access the database. For further reference in this document, the original Front-End file may be referred to as the "template".

The Back-End file is best saved to the same location as the original Access File. This will ensure that all users who could already reach the Access File will not be disrupted. The Back-End also needs to be kept in a location that is accessible for all users who have the copy of the Front-End as both files work in tandem.

Compacting and Repairing

Compacting and Repairing a database has a couple benefits:

  • It reduces the size of the file by eliminating deleted and temporary items.
  • It re-indexes the tables, making queries and load times faster.

It is recommended that the User's local copy (As referenced in Step 3 of "To Split a Database) is set to compact on close in order for it to be compacted regularly. Back-End files should also be Compacted and Repaired regularly. Be sure to create an up-to-date backup. Make sure there are no open front ends and only one open instance for the Compact and Repair operation.

How to Split a Database

Microsoft Access comes with a built in tool to split the database. The steps are as follows:

Before Splitting a Database

Be sure that:

  • There are no users accessing the database.

  • A backup is made of the database. It is easy enough to copy the database into the same folder and rename it with a trailing _backup for identification.

To Split a Database

  1. Open the Access Database that you would like to split and click "Access Database" under the Database Tools tab.

    AccessToolbar.PNG

    • Figure 1

  2. Click "Split Database". Then choose where you would like to save the back end file. We recommend saving to the folder where the original database is located. We also recommend saving with the name of the Database and a trailing "_be" to signify that it is the Back-End. Both recommendations are default actions. The Front-End will keep the original name of the Database. This is shown in Figure 2 below. It is best to save the Back-End file to the same folder that the original is located in.

    SaveBE.PNG

    • Figure 2

    FileExplorer.PNG

    • Figure 3

  3. In figure 3 we see the original database file, for this example it is Database1.accdb. It is now the front end file. The newly saved back end file is Database1_be.accdb. As detailed in our best practice section it is recommended that the back end file be left on an accessible location and the front end file be Copied to all users' computers. When the user would like to work with the database they would open the copy saved on their local machine in the same way as with any other Access Database File.

Linking New Tables

Because the Database is split when new tables are created in one file they are not created in others. The best way to alleviate this issue is to create tables exculsively in the Back-End file and then link to the template Front-End. Then re-distribute the front end file accordingly. This will show the process.

  1. In Figure 4 below we have two files, the Front-End, Database1.accdb and the Back-End, Database1_be.accdb. First, open the Back-End file. In Figure 5 I have create a new table named New-Table.

    FileExplorer.PNG

    Figure 4

    NewTable.PNG

    Figure 5

  2. Now, open the Front-End template. This is the Front End file located in the same folder as the Back-End file. In this example it is Database1.accdb. As shown in Figure 6, Click "Access" under the "External Data" Tab. Also, note that the newly created table New-Table does not exist in the front end.

    NewFrontEnd.PNG

    Figure 6

  3. In the new window click "Browse" and select the Back-End file. Once the Back-End is selected the the value in "File name:" will update to the path of the Back-End. The select the button for "Link to the data source by creating a linked table". Then click "OK". This is shown in the Figure 7 below.

    ImportTables.PNG

    Figure 7

  4. Select the tables to be linked from the new window. More than one table can be linked at the same time. In our case we will select New-Table. Then click "OK". See Figure 8 for an example.

    LinkTables.PNG

    Figure 8

  5. The tables are now linked. This means that what is entered in the Front-End table is reflected in the Back-End table and vice versa. In Figure 9 notice the blue arrow by the New-Table. This signifies that the table is linked from an external source.

    LinkedTables.PNG

    Figure 9

  6. With the tables linked to the template Front-End they will have to be re-distributed to all users. Again, it is best to only Copy the Front-End file, in the event that more tables need to be linked.

Deleting Tables

As noted in the previous sections the Front-End file only has links to the Back-End. Therefore, when a table is deleted from the Back-End the Front-End cannot find the table as it they longer exist. To properly delete a table follow the instructions below:

  1. Verify that all the Front-End files are closed.
  2. Open the Back-End file.
  3. Delete the specified table from the open Back-End
  4. Close the Back-End file.
  5. Open the Front-End and delete the specifed table.
  6. Re-distribute the updated Front-End.

Please Note: If a table is deleted, or unlinked from a Front-End file it will still be retained in the Back-End.

Other Resources

Microsoft's How to Split a Database

Microsoft's Compact & Repair Guide

TSSHowTo