<

Go Premium for a chance to win a PS4. Enter to Win

x

Splitting Microsoft Access Database with Multiple User

Published on
4,606 Points
1,206 Views
4 Endorsements
Last Modified:
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
Comment
Author:Jason clark
2 Comments
 

Expert Comment

by:Shan G
Thanks, this was a great stuff.
1
 

Expert Comment

by:Manish
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.
1

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Join & Write a Comment

In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month