다음을 통해 공유

Splitting an Access Database with Multiple Users

  • 아티클

Multiple users accessing the same Access database can impact the database performance. Also, there is a risk of database corruption and data loss. Splitting the database helps improve the performance, as only data (without objects) is shared over the network. Plus, corruption in the database file is limited to the front-end, so the back-end is less likely to get corrupted. This article will discuss some other benefits and steps to split an Access database with multiple users.

Splitting Access Database – Additional Benefits

Following are the additional benefits you get when splitting an MS Access database with multiple users:

  • Better Data Availability – In a shared database that is split, only the data is sent across the network. This helps complete database transactions like editing table records more quickly, making more data available for editing.
  • Flexibility – Since each user gets to work with a front-end database copy locally, a user has the flexibility to develop queries, reports, or any other db objects without disrupting the other users. Likewise, an updated version of the front-end db can be created and distributed without disrupting users from accessing the data in the back-end database.
  • Enhanced Security – Because each user interacts with the back-end database via linked tables, there are fewer chances that hackers can access data by attacking the front-end database.

Steps to Split an Access Database

Access comes inbuilt with a 'Database Splitter wizard', allowing the users to split a database. However, before discussing how to use the wizard to split a database, there are a few considerations you need to take into account:

  • Ensure to back up a database before splitting it. Doing so will help you restore the db from the backup if you no longer want to split the db.
  • Notify the users from using the database before splitting it, as the changes won't reflect in the back-end db.
  • Ensure that the MS Office Access version is compatible with the back-end database file format. 
  • Avoid sharing copies of an Access db containing links to Sharepoint links to prevent any malicious user from modifying the Sharepoint site permissions.

Steps to split a shared Access database are as follows**: **

  • Back up the database you wish to split on your PC.
  • In MS Access, open the backup copy of the database.
  • Click Database Tools from the top toolbar, then click Access Database from the ‘Move Data’ section.

 

  • When the Database Splitter wizard opens, hit the Split Database button.

 

  • In Create Back-end Database dialog box, enter filename of your back-end database and choose the location to save the file. 

Note: It is recommended that you must use ‘_be’ as a suffix in the original filename. The file with ‘_be’ in its name is the back-end database. In the File name textbox, you may also enter the path of the network location where the back-end database is stored. For instance, if the back-end database location on network share is \server1\dbshare\ and the file name is dbname_be.accdb, you can specify \server1\dbshare\dbname_be.accdb in the File Name textbox.


 

  • Click the Split button.

The database is now split. The original file is your front-end database, and the file with ‘_be’ in its name is the back-end database. 

Conclusion

When sharing an Access database across the network, you may face performance issues and the risk of data loss. Splitting a shared Access database can help improve performance, ensure better data availability, provide flexibility to develop and distribute front-end db. The biggest benefit of splitting a database is that it secures a database from getting corrupt. In case your split database becomes corrupted, you can repair the database using an Access database repair tool such as Stellar Repair for Access.