Split an Access database

Applies to: Microsoft Access 2010, Access 2007

Print

Show All

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.

After you split database, you may decide to move the back-end database, or to use a different back-end database. You can use the Linked Table Manager to change the back-end database that you use.

Notes

• • Splitting a database is not the same as archiving a database. This topic does not discuss how to archive data. You archive data by periodically moving older records to a different database, either because the database file grows too quickly to the file size limit, or because you want to keep the database file small and your data cleanly organized by time period. In some circumstances, you can archive data by splitting a database. For more information, see the online article Archive Access data.

  • If you split a web database, any web tables in that database will not be moved to the back-end database, and will not be reachable from the resulting front-end database.

In this article

• Overview

• Before you begin

• Split the database

• Distribute the front-end database

• Change which back-end database you use

Overview

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.

To split a database, you use the Database Splitter Wizard. After you split the database, you must distribute the front-end database to your users.

Caution To protect your data if your database has multiple end users, it is recommended that you do not share copies of a database that contains links to SharePoint lists, including links to lists within a published web database. If you link to a table that is a SharePoint list, it creates a possibility for any malicious user to change the target of the link and potentially modify permissions on the SharePoint site as the connection information for linked tables is unencrypted.

Benefits of a split database

The benefits of a split database include the following:

• • 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.

  • 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.

  • Enhanced security if you store the back-end database on a computer that uses the NTFS file system, you can use NTFS security features to help protect your data. Because users access the back-end database by using linked tables, it is less likely that intruders can obtain unauthorized access to the data by stealing the front-end database or by posing as an authorized user. By default, Windows XP, Windows Vista, and Windows Server 2003 use the NTFS file system. If you are not sure what file system your file server uses, ask the system administrator. If you have administrator privileges on the file server, you can run the msinfo32 command to determine the file system yourself.

Top of Page

Split the database

• 1. On your computer, make a copy of the database that you want to split. Start with the database file on your local hard drive, not on the network share. If the database file is currently shared from your local hard disk drive, you can leave it where it is.

  2. Open the copy of the database that is on your local hard disk drive.

  3. On the Database Tools tab, in the Move Data group, click Access Database. The Database Splitter Wizard starts.

  4. Click Split Database.

  5. In the Create Back-end Database dialog box, specify a name, a file type, and a location for the back-end database file.

Notes

• • Consider using the name that Access suggests. It preserves the original file name, and indicates that the database is a back-end database by inserting _be into the name, just before the file name extension.

  • Do not change the file type unless some users will use an earlier version of Access to access the data.

  • You can enter the path to the network location in the File Name box, in front of the file name. For example, if the network location for the back-end database is \\server1\share1\ and the file name for the back-end database is MyDB_be.accdb, you can enter \\server1\share1\MyDB_be.accdb in the File Name box.

  • The location that you choose must be available to everyone who will use the database. Because drive mappings can vary, you should specify the UNC path of the location instead of using a mapped drive letter.

• 1. When the wizard finishes, it displays a confirmation message.

Your database is now split. The front-end database is the file that you started with (the copy of the original shared database), and the back-end database is located in the network location that you specified in step 5 of this procedure.

Restrict changes to the design of the front-end database

To restrict changes to the front-end database that you distribute, consider saving it as a compiled binary file (an .accde file). In Access 2010, a compiled binary file is a database application file that has been saved with all the Visual Basic Access (VBA) code compiled. No VBA source code remains in an Access compiled binary file. Users cannot change the design of objects in an .accde file.

• 1. Open the front-end database file (.accdb) that you want to save as a compiled binary file (.accde).

On the Database Tools tab, in the Database Tools group, click Make ACCDE.

• 1. In the Save As dialog box, browse to the folder where you want to save the file, type a name for the file in the File Name box, and then click Save.