Quick Guide to Splitting an Access Database

If you are going to make your Access database available to many users who will access it over a network, it is advisable to split the main MDB file into two files. One is the back-end that will contain all the data tables and records and the other, the front-end that will contain other database objects such as forms, queries and reports. The front-end can be distributed to all the users by installing it on each workstation while the back-end is stored securely in one place on a server and shared amongst the users. Each user can easily interact with the back-end by using their local copy of the front-end. By focusing on the back-end that will contain all the important data, you can ensure proper backups are taken so that when one needs to repair access, all the data is easily recoverable.

There are many benefits to splitting an MDB file in addition to better security for data -

" Improved efficiency - When the database is not split and the entire MBD file is being shared amongst users, both the data and the database objects such as queries, forms, reports, modules, etc. are sent across the network. This reduces the speed with which the database can operate not to mention there being a greater chance of the data or objects getting corrupted during transit. By storing all the database objects locally, only the data has to be sent across the network improving the efficiency of the database. It is also easier to take backups of the front-end locally so that if any of the objects get corrupted, one can quickly repair access objects.
" Greater security - When the back-end of the database that contains all the vital data is stored separately, one can use enhanced security features to protect this data. The back-end database is accessed through linked tables that makes it difficult for intruders to either pose as an authorized user or to steal the front-end.
" Improved speed - When the front-end is stored locally, only the data has to be sent across the network and this improves the speed with which various database transactions especially record writes or edits are completed.
" Better reliability - Sudden network failures can cause a database to close unexpectedly; this usually causes some corruption in the database file that is open. However, when the MDB file is split into two, the user will typically have only the front-end open limiting the corruption to just the front-end. This makes it easier to repair access files.
" Reduced risk - Though the likelihood of the back-end in a split database getting corrupted is less, one can make sure that the data remains secure by understanding how specialized software such as Advanced Access Repair works. They can very quickly scan and restore highly damaged MDB files making them a very useful tool to have.
" Greater flexibility - By having a front-end that is independent of the data, it is easier to improve on the front-end alone and distribute it without having affecting the data in any way.