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.

 Note   Splitting a database is not the same as archiving a database, and 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 article Archive Access data.

---

• Overview

• Before you begin

• Split the database

• Distribute the front-end database

---

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.

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.

  How do I use msinfo32 to check the file system?

  1. Click the Start button, and then click Run.
  2. In the Run dialog box, type msinfo32 and then click OK.
  3. Under System Summary, click the plus symbol next to Components.
  4. Under Components, click the plus symbol next to Storage, and then click Drives. The dialog box displays information about the available drives in the panel on the right.
• 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.
• Flexible development environment  Because each user works with a local copy of the front-end database, each user can independently develop queries, forms, reports, and other database objects without affecting other users. Similarly, you can develop and distribute a new version of the front-end database without disrupting access to the data that is stored in the back-end database.

 Top of Page

Before you begin

Before you split a database, consider the following:

• You should always back up a database before you split it. If you split a database and then decide that you did not want to split the database, you can restore the original from your backup copy.
• Splitting a database might take a long time. You should notify users so that they do not use the database while you split it. If a user changes data while you split the database, the changes will not be reflected in the back-end database.

   Tip   If a user does change data while you split the database, you can import the new data into the back-end database after you finish.

• Although splitting a database is one way to share data, everyone who uses the database must have a version of Microsoft Office Access that is compatible with the file format of the back-end database. For example, if the back-end database file uses the .accdb file format, users cannot access its data by using Access 2003.
• You might want to use an earlier Access file format for the back-end database if you use features that are no longer supported. For example, if you use data access pages (DAPs), you can continue to use them if the back-end database is in an earlier file format that supports DAPs. You can then use the new file format with the front-end database so that your users have access to the benefits of the new format. Note that you cannot change data in a Data Access Page by using Access 2007. For more information, see the See Also section.

 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 (UNC: Acronym for Universal Naming Convention (also Uniform Naming Convention). The system of naming files among computers on a network so that a file on one computer will have the same pathname when accessed from any of the other computers on the network.) path of the location instead of using a mapped drive letter.

6. 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 2007, 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).
2. On the Database Tools tab, in the Database Tools group, click Make ACCDE.
3. 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.

 Top of Page

Distribute the front-end database

After you split your database, you distribute the front-end database to your users, so that they can start to use the database.

Do one of the following:

• Send an e-mail message to the database users and attach the front-end database file to the message. Include any instructions that will make it easier for your users to start to use the front-end database immediately.
• Save the front-end database file to a network location that all database users can access, and then send your users an e-mail message that specifies the network location together with any other instructions they might need in order to access the database.
• Distribute the front-end database file by using removable media, such as a CD-ROM or a USB thumb drive. If you install the file yourself, you can test it to make sure that it works. If users must install the file, you should include a document that explains to them what they must do to install the file, and who to contact if they encounter difficulties.

 Top of Page