Splitting Microsoft Access Database with Multiple User

Jason clarkDBA Freelancer
Published:
Updated:
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and keep database secure from corruption.

Splitting Microsoft Access database can help in improving performance and reduces corruption chances. Splitting MS Access database into a front end design and a back end design is the most important design. This enables you to separate your application and data with an option to upgrade MS Access application without deleting data or adding any malcious data.


Overview


To Split an Access database there is need to organise existing database with its objects such as tables, reports, macros, forms, nodules, etc. into two database files:

  • A Back end database is that which contains the tables
  • A Front end database is that which contains the other database objects.

After splitting database local copy Access database front end design is used to interact with multiple user on a shared network. A local copy of front-end database should be distributed among the multiple user. 


Benefits of Split MS Access Database 


Here we have discussed some of the major benefits of splitting Microsoft Access database:

  • Enhanced Performance: Performance is improved and chances of database corruption decreases because only a copy of data is shared over a network. By this each user has the copy of front-end database installed on there system rather then using it over a network which reduces the traffic.
  • Boost Security: As each user access the back-end database through linked tables, so there is less chance that any unknown user can get access to data by stealing the front-end database.
  • Data Availability:  Only the data is shared across the network, which leaves data available to edit by making database transaction (editing records) complete more frequently.  
  • Improved Reliability & Minimize Corruption: If due to any reason user encounters an issue and database get closed unknowingly, chances of database file corruption increased but it is usually limited to the copy of front-end database which is opened on the user system. Because user access the back-end database with the help of linked tables so, there is less chance that back-end database get corrupted.
  • Independent Environment: Every single user can develop database objects independently because all user works with the local copy of the front-end database. Similarly, a new front-end database can be developed and shared without interrupting back-end database.  


How To Split Access Database?


Using Database Splitter wizard user can split Access database. Best way to split Microsoft Access database is described in steps:


  1.  Make a copy of the database on your local hard drive.
  2.  Open that copy of the database.
  3.  GOTO Database Tools tab --> in Move Data group --> click MS Access Database. Now Split Wizard starts.
  4.  Click on Split Database.
  5.  Create Back-end Database will Pop-up, In this specify name, file type, and location for back-end database file.
  6.  Confirmation message will Pop up after successful completion.

Now, the database is split, Front-end database is the file you started and the back-end database is at the location specified by user.


Various Ways to Distribute the Front-end Database 


  • Through emails, user can attach the front-end database file and send with email messages.
  • Save front-end database file to the network location where all user can access.
  • Or it can be distributed using removable media


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.


Conclusion


Splitting MS Access Database is discussed above in brief with its benefits. Although, It is a safe method to share Access database. But there are several possible reasons which may corrupt database and to fix corrupt Access database files user need to repair MDB/ACCDB files using Inbuilt Compact & Repair Utility. 

4
4,486 Views

Comments (2)

Commented:
Thanks, this was a great stuff.
JohnTechnical Consultant

Commented:
Thanks, this was a comprehensive article.
Many no. of times user fails to repair corrupted MDB file with Compact & Repair utility . This is the phase where user might get stuck. In such condition User may try 3rd Party Access database repair tool to fix MS Access database corruption error.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.