Splitting Microsoft Access Databases to Improve Performance and Simplify Maintainability
Provided by Aparna Pophale, Quality Assurance Specialist
Microsoft Access lets you easily create databases to store and present your data in forms and reports. When starting, a database may be very simple and trivial, but over time, it may become more critical as you add more data, features, and even share it with others. It gains a life of its own and the overall design becomes critical.
One of the most important architectural designs is splitting the database into a front-end and back-end database. This is the way Access was designed to let you support multi-user databases and significantly simplify how you enhance the application over time.
A Split Database Design: Front-End/Back-End Databases
Splitting a database is a relatively simple concept. You take an existing Access MDB/ACCDB database with its tables, queries, forms, reports, macros, modules, etc. and divide it into two databases:
This design is especially useful in multi-user environments where the back-end database is stored on a network and contains the shared data. Each user then has a copy of the front-end database on their desktop pointing to the shared database.
- The “Back-End” database just contains the tables
- The “Front-End” database contains the application objects (everything except the tables) and links to the tables in the back-end database
In multi-user environments, the front-end database can also contain tables that are private to the user. These local tables can store the user’s settings, selections, temporary or intermediate tables for processing data or reports, etc.
Reasons to Split a Microsoft Access Database
Here are some of the major reasons to use a split database architecture
How to Split Your Microsoft Access Database
- Without a split database architecture, you’ll need to update the database with the latest data, people have changed with every new release.
- Application enhancements are simplified since they are made in the front-end database without worrying about changes to the data in the back-end database. Releasing new versions and bug fixes becomes much easier since only the application part needs to be distributed. Of course, if you modify table structures or add/delete/rename tables, you’ll need to apply those changes to the back-end database.
- Performance can be significantly enhanced and network traffic reduced when the user has a copy of the front-end database installed on their desktop rather than running it off the network each time they use it.
- Temporary tables can be kept for each user in their front-end database. This avoids collisions among multiple simultaneous users if they were all using one database.
- Without splitting a database, multiple users running the same database on the network increase the chance of database corruption. The split database design minimizes this problem and avoids code corruption from impacting data corruption.
- This simplifies database administration since the data is stored centrally and can be backed up and compacted. A single master front-end application database is copied to each user’s machine, but is not necessary to back up.
- Provides an opportunity to expand a database size beyond the 2 GB size limitation of Access since the front-end database can link to multiple back-end databases if necessary.
- Sets the stage for migration to SQL Server. If the application evolves to need the features of SQL Server, you can still use the front-end database to link to data stored in SQL Server.
You can manually split your database by:
Or, you can use the Microsoft Access Database Splitter Wizard to split the Access database. Consider this example:
- Copying it
- Deleting all the non-table objects from one of them and make that your back-end database
- Delete all the tables from the other “front-end” database, then link to the tables in the back-end database.
Open a Tasks template form Microsoft Access 2007. The Tasks database is designed with tables, queries, forms and reports. The database contains 3 tables Contacts, Filters and Tasks. To open a Database Splitter, select Database Tools tab from Access ribbon and click on Access Database option.