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
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. 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. 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?
-
Click the Start button, and then click Run.
-
In the Run dialog box, type msinfo32 and then click OK.
-
Under System Summary, click the plus symbol next to Components.
-
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.
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 2010 or higher.
Split the database
-
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.
-
Open the copy of the database that is on your local hard disk drive.
-
On the Database Tools tab, in the Move Data group, click Access Database. The Database Splitter Wizard starts.
-
Click Split Database.
-
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.
-
-
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). 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.
-
Open the front-end database file (.accdb) that you want to save as a compiled binary file (.accde).
-
Click File > Save As > Make ACCDE > Save As.
-
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.
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.
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. 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.
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.
Change which back-end database you use
You can move your back-end database, or use a different back-end database, by using the Linked Table Manager.
If you want to move your back-end database, first make a copy of it to the new location, and then follow this procedure.
-
On the External Data tab, in the Import & Link group, click Linked Table Manager.
-
In the Linked Table Manager, select the tables that are in the current back-end database.
Tip: If you have not linked to any other databases, click Select All.
-
Select the Always check for new location check box, and then click OK.
-
Browse to and select the new back-end database.
