Daily Access Tips

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

First let’s start with the definition of a split MS Access Database:

A Microsoft Database that is split into a front-end database that contains the application (queries, forms, reports, macros, and modules) that are linked to MS Access tables stored in a back end shared database file containing only the data tables.

You will split MS Access Database when moving an application to a multi-user environment rather that have everyone using a single file on a shared network drive.  Doing otherwise puts your data at risk of corruption.  However, as you will find out in the next article, even a single user database will benefit from being split.

When you split a database, creating a front end and a back end, the back end Microsoft Database file is stored in a shared folder on a network drive and you link Microsoft Access data to MS Access front-end file. The front-end database file is distributed to each user’s desktop.  You would link Microsoft Access front-end to MS Access back-end, just prior to distribution. Using this design, each user has a copy of Microsoft Database program installed on their workstation machine along with their front-end application database file. Each user interacts with the data by using a local copy of the front-end Microsoft Database file. This reduces network traffic since the front-end does not need to be sent over the network path for each use, and allows the front-end database to contain settings or temporary data specific to the individual user. This split database design also allows development of the application, the front-end, to be totally independent of the data.  This allows for front-end to be easily replaced without affecting the data in the back end database. You will at that time link Microsoft Access front end file to MS Access back end file using the Linked Table Manager or some code to do it. The Microsoft Database program has two built-in utilities (Database Splitter and Linked Table Manager) to aid in your linking to MS Access data.  Look-up tables that rarely change their data are also possible items for the front-end. However, if you have tables with fields that look-up to other tables, those tables should be in the same back end database. Otherwise, if you open that source database and that table, it’s look-ups will not appear and this can cause data entry and data integrity problems.

The next article in this series will go over the risks when you do not split MS Access Database or link Microsoft Access front-end file to MS Access back end file.  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.

In the next article you find out why it’s vital to Link Microsoft Access Application to MS Access back-end data file even if you only have one person using the database.

Continue to part 2: http://www.dailyaccesstips.com/why-link-microsoft-access-application-to-ms-access-backend-data-file-part-2-of-4/