Link Microsoft Access to Prevent Database Corruption

Link Microsoft Access to Prevent Database Corruption

Sharing a single database file over the network, rather than split MS Access Database, has been found by Microsoft to be the number one cause of database corruption.

A common cause of corruption is a dropped network connection when writing to MS Access back-end file.  When multiple users open a single Access database file on the file server, the chance that a network connection being dropped when one of them is writing data file to MS Access back-end is much higher than when each of these users opens his own copy of the front-end Access database file.

The benefits of splitting the database are also presented in this series of articles showing how to split MS Access Database or link Microsoft Access front-end file to MS Access back end file.  How you will get increased performance, better data integrity, data security and less maintenance headaches are fully covered.  The pros and cons of using a wizard to do the split for you will be discussed along with how to manually accomplish this procedure when the wizard would not do the job adequately. 

At the end of the series we will discover code written by an Access MVP Microsoft Most Valuable Professional by the name of Dev Ashish. This code ensures that the data linking is working each time the program starts.  You will also be directed to a Microsoft KB Knowledge Base Article to further illustrate a manual method of splitting and linking your database.

Data secruity should also be a concern along with database corruption issues. Since you link Microsoft Access application file to MS Access back-end file located on a central server, different users may be assigned with different levels of permission. You may assign some read only permission to your database while assigning others read and write permissions. The server can be used to grant various permission levels for the MS Access link to the network folder. Your network or system administrator can set these for you.

Since the chances of data corruption are increased with poor network writebacks you should also consider the performance issue. The performance of the database will greatly improve when you link Microsoft Access application file to MS Access back-end file containing only Access data tables. The reason is that only the data is sent across the network. In a shared database that is not split, the database objects themselves (tables, queries, forms, reports, macros and modules) must be sent across the network, not just the data. Because only the data is sent across the network, database record transactions such as record edits are completed faster. This reduces network traffic and that results in greater network speed. Loading the entire application (forms, controls, code, etc) locally and using only an MS Access link to your data will make your application interface run faster because the form or report is sitting on the local workstation hard drive saving time retrieving it across the network.