Objective 1.4 Protect and maintain databases

  • 1.4.1 Compact and repair databases
  • 1.4.2 Back up and restore databases
  • 1.4.3 Split a database
  • 1.4.4 Encrypt database files

OBJECTIVE 1.4: Contents

1.4.3 Split a database

You can think of an Access database as composed of two parts. The tables, which define and store the raw data, make up one part (the back end). The second part consists of the queries, forms, reports, and supporting objects that users interact with to enter, edit, and view data (the front end). You can split a database to store the tables in one file and the other database objects in a second file. Splitting a database into a front end and back end allows the database designers and administrators to create new forms or update reports for the front end without interfering with the use of the back-end database while doing so. When the new objects are ready, the administrators can implement the updated front end without affecting the relationships and references in place in the back end.

Another reason to split a database is to reduce network traffic when the database is used by more than one person. The back-end database (the tables) can reside in a shared location, and each person can use a local copy of the front-end database.

Tip: You should back up your database before you split it.

After you split a database, Access displays a small arrow icon in the Navigation Pane next to the names of tables.

Screenshot of the Navigation Pane with linked tables indicated by arrow icons.

The arrows in the Navigation Pane indicate tables that are linked to the back-end database

To split a database

1. Close all open database objects.

2. On the Database Tools tab, in the Move Data group, click Access Database. If Access displays a security notice that shows the path to the file ACWZTOOL.ACCDE, click Open to proceed and open the Database Splitter dialog box.

3. In the Database Splitter dialog box, read through the information provided, and then click Split Database.

Screenshot of the Database Splitter dialog box.

The Database Splitter

4. In the Create Back-end Database dialog box, choose a location for the back-end file. By default, Access uses the current database name and adds _be to the end of the file name.

5. Click Split to begin the process. When the process completes, in the Database Splitter message box confirming that the database split was successful, click OK.