With the continuous use of Microsoft Access database, size keeps growing. When it become very large, the database (either MDB or AccDB) may start performing slowly, freezing, and crashing. In such cases, you should consider splitting the database. You may also need to split it if multiple users access it simultaneously over the network. Splitting improves the performance of the database, minimize the possibilities of database corruption, and prevent need of MDB repair solutions.
You should not confuse database splitting with archiving. However, in some situations, it is possible to archive the database by splitting it. When a database is split, it is organized into two different files - one back-end Access file that holds only data tables and one front-end file that holds all other objects like forms, queries, reports, modules, and VBA codes. The user directly interacts with data using local copy of front-end Access database.
Advantages of Access Database Splitting
Enhanced Performance- The database performance is significantly improved after splitting because only data is sent throughout the network. You need not to use objects over the network.
Improved Security- When you store database on back-end, you can use the security features of your local file system. The back-end database is accessed through the linked tables and unauthorized access is prevented using file system security options.
Enhanced Reliability- If you run across any problem and your database is terminated improperly, corruption is generally limited to copy of front-end database that is stored locally on your computer. Because back-end database is accessed only through the linked tables, chances of corruption are very limited.
Greater Database Availability- Since only data is sent throughout the network, Access file transactions like record editing are accomplished more quickly thus leaving your data more usable for editing.
In case you encounter corruption on a split database, you can easily recover Access database by restoring it from the most recent backup. For absolute recovery, the backup must be validated.
by Ericc Justice
