This article is the procedure was created specifically for Cathy Comber who has an access database that the Chemistry Admin Staff reference
and Update. However it might be useful for any one wanting to set up a an Access database for multiple simultaneous access for a limited number
of users ( Ideally up to 10). Larger databases should be ported to MS SQL Server or MySQL.29 Oct 2013
Splitting An Access Database for Multi-User access
Reasons Why You May Want to Split Your Database
The following are typical reasons to split a database:
- You are sharing your database with multiple users on a network.
- You have several people developing in the database and you do not have Microsoft Visual Source Safe installed.
- You do not want your users to be able to make design changes to tables.
The most common reason to split a database is that you are sharing the database with multiple users on a network. If you simply store the database on a network share, when your users open a form, query, macro, module, or report, these objects have to be sent across the network to each individual who uses the database. If you split the database, each user has their own copy of the forms, queries, macros, modules, and reports. Therefore, the only data that must be sent across the network is the data in the tables.
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 the database in Microsoft Office Access 2007, follow these steps:
- Create a new blank Access database.
- On the External Data tab, click Access in the Import group.
- In the Get External Data dialog box, click Browse to locate and select the database that you want to split, click to select the Import tables, queries, forms, reports, macros, and modules into the current database. check box, and then click OK.
- In the Import Objects dialog box, clickSelect All on theTables tab, and then click OK.
Noticethat Access imports all of the tables into the new database, which is your back-end database.
- Store the new back-end database on a network share, and make sure that all the users have full permissions to the share.
- Create a second new blank Access database.
- On the External Data, clickAccess in the Import group.
- In the Get External Data dialog box, click Browse to select the back-end database that you created, click to select theLink to the data source by creating a linked table. check box, and then click OK.
- In the Import Objects dialog box, click Select All on theTables tab, and then click OK.
Notice that Access links the tables in the back-end database to the front-end database.
- On the External Data, click Access in theImport group.
- In theGet External Data dialog box, clickBrowse to select the original database that you are splitting, click to select theImport tables, queries, forms, reports, macros, and modules into the current database. check box, and then click OK.
- In the Import Objects dialog box, click Select All on theForms tab, repeat this step on all other tabs except the Tables tab because you have already linked to the tables. You now only need to import the rest of the objects, and then click OK.
Notice that you now have all the tables linked and have imported the remaining objects.
- This database is the front-end database. You can distribute this front-end database to the workstations so that each user has his own copy of the front-end database.
- You have now successfully split your database, which will improve performance. Now when a user opens a form, the form opens locally on their computer, and is not sent across the network. The only data that comes across the network is the data in the linked tables.
Splitting Access 2010 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.
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.
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.
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 \server1share1 and the file name for the back-end database is MyDB_be.accdb, you can enter \server1share1MyDB_be.accdb in theFile 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). 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.
- 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, clickMake ACCDE.
- In the Save As dialog box, browse to the folder where you want to save the file, type a name for the file in theFile 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, 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.
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 Database Tools tab, in the Database Tools 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.