Daily Access Tips

If you’re new here, you may want to subscribe to my RSS feed. Thanks for visiting!

Here are the main benefits of a split database. (See Split Database definition in Part 1)

Data Integrity:

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.

If a user, using an MS Access link to data, encounters a problem and the database closes unexpectedly, any database file corruption is usually limited to the copy of the front-end database that the user had open.  Because the user will link Microsoft Access application file to MS Access back-end file database by using linked tables, the back-end database file is much less likely to lead to MS Access data corruption.

Improved Performance:

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.

Maintainability:

Because each user works with a local copy of the front-end database linked to MS Access back-end, each user can develop additional queries, forms, reports, and other database objects without affecting other users. They just need to ensure these changes get added to the next distributed version of the front-end.

Another advantage, individual users can customize some of the settings in Access without affecting other users’ settings. Similarly, distributing a new version of the front-end database will not disrupt access to the data that is stored in the back-end database. So it’s easy to maintain and update the program by just replacing the application file and link Microsoft Access front-end to MS Access back-end. Since the data is in a separate file, no data will be overwritten.

Greater Security:

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.

Before you start splitting your database, you need to choose the best method.  Read the next article in this series to find out what’s best for you. http://www.dailyaccesstips.com/manually-link-microsoft-access-to-ms-access-data-part-3-of-4/