Split MS Access database means to create a front and back-end database. The back-end database contains only the database tables & place in a shared folder on the network. It is linked to the front-end database file. Front-end database contains all objects like queries, forms, reports, macros and modules & distribute to every individual access users to their workstations.
Benefits for Splitting:
- The primary benefit to split access database is to allow multiple users to access the application.
- All access database users can share common data across the network.
- Multiple users can update data simultaneously.
- It helps to improve access database performance and reduce the chance of access database file(mdb & accdb) corruption
- Always take backup of the database before you split it. If anything goes wrong, you can restore it by backup.
- Do not use the database while you split because it might take a long time. If you split database, at the same time a user make any changes in the database, the changes will not be updated in back-end database.
- Every user must have a version of MS Office Access that is compatible with the back-end database file format.
- You can split a database into 2 files - one that contains the tables, and one that contains the queries, forms, macros, and modules.
How to Split:
You can use Database Splitter Wizard to split access database.
For Access 2000 – 2003:
Tools>>Database Utilities>>Database Splitter
Now follow these instructions in Database Splitter Wizard.
For Earlier Access versions:
Follow the instructions in the Database Splitter Wizard.
Note: After splitting the database, distribute the front-end database to all users who used access database. Access database with all database objects will be installed on each client machine. The database that contains the source tables will be installed on the server.