By splitting your Access database you separate the application into the front end and data (tables) into the back end. This is a good practice for all software including Access applications. This is why I recommend that every database should be split into an application/front end and data/back end. It is not just for multi-user applications/databases. I always split a database from the start. Even if it is just for a single user on a standalone computer.

Splitting a database is not just for multi-user databases.

It's about good software design!

Why you should split every  database used by multiple users at the same time

An Access database should never be opened by more than one user at a time. What? Access is supposed to be able to create a multi-user database. I can be used for multi-user applications. The way you do this is to split the database into an app (front end) and data (backend). Each user will have a copy of the app (front end) that is not shared. The data (back end) is shared by having each user’s app (front end) link to the tables is the shared back end.  Only the app (front end) is actually opened by Access directly.

Good Multi-User software is split into  Application and Data

I don’t know of any good multi-user software that is not split into a front end and back end.  All software that uses an RDBMS like an SQL Server, is split. The RDBMS is the data or back end.  This Includes web sites A web site is a back end. The browser on each user’s PC/device/smartphone is the front end. The front end is the ASP/PHP/web scripts that generate the HTML to be sent to the end user's PC/device.

When to split your database?

While you might think it is a good idea to wait until you are ready to deploy your database to split it, I urge against this. I actually find that having a split database during the development and testing phases also aids in many ways.

Some reasons why I start every database split:

• Most importantly, it helps protects the data!
• Easily switch between back ends for better testing!
• Designing in the same environment that the application will be used allows better testing as you build the app.
• easy to test ideas on copies
• backups are easier
• you avoid the need for retesting everything later after the app is split
• Deploying updates to Forms, Reports, Queries, and Modules is as easy done by replacing the Application (Front end) file.
• If every user has their own copy of the front end then you avoid issues with different versions of Access being used at the same time.
• You can exceed the 2 GIG database file size limit by splitting your data into multiple back ends.

With a split database, your app (front end) can be 2 GIG and your data (back end) can be 2 GIG with a single back end.  This allows you to safely exceed the Access 2 gig limit for a single database.If you have some very large tables you could split your data into multiple back ends. The downside is that you can't have the database engine enforce Referential Integrity between tables in separate databases.  By splitting your data into multiple back ends, your data can safely exceed the 2 gig limit for a single Access database. When your data grows to this size you really should consider upsizing your data into an SQL Server.

How it helps to protect your data

Having your application and data split adds another layer of protection for your data. How is this?

Several ways. Here are a few:

1) When you forget to make a backup and you run the compact and repair and it corrupts your front end database. You will not lose your data in the back end.

2) You can develop and properly test by using a copy of your data (back end). When you are ready to deploy you relink to the production/live data.

I am sure you would never do this:
While testing you run an update or delete query and the WHERE condition was missing or incorrect. Oops ... now you just deleted all your data or updated all the records when it should have been just a few. Not a problem if you are using test data ( back end).

Protecting your Application (Front End)

When you are ready to deploy your database, you can help protect your application by compiling the application ( front end) into a MDE/ACCDE. This will prevent anyone from entering design mode for Forms, Reports, and Modules. You want to be sure you keep the original MDB/ACCDB since this is your source. If you want o make any changes you must use the original MDB/ACCDB. When your changes are completed, you will create a new replacement MDE/ACCDE.

Locking Down the Front End

I do not normally lock down the front end until I have made the ACCDE/MDE.  If running in with the Access Runtime version of the Full version in Runtime mode it will automatically be locked down. I still go through steps to lock down the front end for when opened with the Full version of Access.

Another reason to use an MDE/ACCDB

With Access in runtime mode (full version or the runtime version) running an MDB/ACCDB you must make sure that you do not have any un-handled/ un-trapped errors. Any un-trapped errors in the runtime mode this will cause Access to shut down (appear to crash) with an MDB or ACCDB. This is not true with an MDE/ACCDB. Also, variables are not reset, etc. It is a lot more stable when errors occur. if you use an MDE/ACCDE.

How to split your database

From the very beginning

I normally start ever Access database with it split. I first create a database with only the tables. When I am ready to start creating the UI, I create a new database for the front end. In the front end database, I create linked tables to the back end database.

Splitting an existing database

You have several options to split an existing database:

Use the built-in Wizard

Use the built-in database splitter. This is the easiest and simplest method.

1)      Make a backup o0f the database.

2)      Make another backup of the database. Just to be safe

3)      Run the database splitter

Manually

You can manually split the database. The way I prefer to  use these steps:

4)      Make a backup of the database.

5)      Make another backup of the database. Just to be safe

6)       Create a new blank back end database.

7)      Import all the tables form the existing database un-split database.

8)      Open the original database and delete all the tables that were imported into the  new back end

9)      Create linked tables to the new back end

also see: How to manually split a Access database in Microsoft Access

Managing your linked Tables

One of the many advantages of having your database split is that it allows you to switch between different back ends.  I use a separate back end for development and testing. This allows me to do fully test without worrying about corrupting the live/production data.

One issue with the linked table that you have to deal with is the fact that Access stores the full pathname to the back end database for each table. This means when you move the back end to a new location you must do one of the following:

1)      Delete all the linked tables. Recreate the linked tables.

2)      Use the Linked Table Manager to change the currently linked tables to a new location.

3)      Use VBA code to relink the tables. You could automatically relink the table to a known location. You could also provide a File browser dialog to allow the user to locate the backend.

For more details on using the Linked table Manger, using VBA code to refresh the links, relinking with the Access Runtime, and VBA code examples check out:

How to Manage Your Linked Tables

How to deploy your split Application

Back end Location

The back end or data will need to be in a folder where the user has read and write permissions. I find it best for performance to keep the folder level to a minimum.  I live to not use any subfolder below the share name.

Example:

\\server_name\share_name\data.mdb

Front End Location

The Application or Front End is best placed on the local hard drive of each workstation. I like to place Applications (front end database)  in the user’s profile folder. This does mean each user will have a copy of the Application. This is true even with a SQL Server back end or on a Terminal Server/Citrix.   I use the %appdata% environment variable to find the location. This works great with XP, Vista, Windows 7, and Terminal Servers.

Placing the Application (front end) in each User's Profile on the local hard drive has several advantages:

• Insured that the Application (Front end) will not be shared
• Reduces network traffic
• Increased performance
• By placing the Application in each user's profile will make migrating to Citrix or Terminal Services a lot easy.

See the links before for tools to help you deploy the Application (front end) to each workstation.

Problems if all users share one front-end file or non-split database

1. Poor performance. Every form and report object goes over the network when accessed generating extra network traffic.

2. Temp tables can’t be shared unless you are generating unique names for each user or making sure that only one user can do the same process at a time. Otherwise, they can collide with one another.

3. Shared FE's (Front ends) have a tendency to corrupt with a lot of users in.  There is a multitude of factors that contribute to this.

4. If you're making any design changes in objects on the fly, then the user needs exclusive access to the DB.

Multiple Versions of Access

When you have different versions of Access installed on machines on the network it becomes very, very important that you split your database and every user has their own copy of the front end. You do not want different versions of Access opening the same front end or you will have a reference issue. If Access 2007 opens the database it will upgrade the Office Library references to the 2007 versions.  If you later try to open the database with Access 2003 the references may not downgrade properly.

Where do I update forms, reports, queries, modules, and macros?

The Application (front end) is where all the forms, reports, queries, modules, and macros are located. Once you have made all the desired changes and have them fully test, it is then time to deploy a copy to all the users.

When I am ready to deploy a new version, I first compile the Application (front end) into an ACCDE or an MDE for 2003 and earlier formats. If while testing, I relink to test data, I open the new ACCDE/MDE and relink the tables. I then go through the steps to lock it down. Once this is done, I copy the ACCDE/MDE to a shared location on the network.

Deploying Application (Front End) Updates

There are several methods you can use to deploy the new Application (front End) version to all the users. Here are some:

1) Manually copy the file from the server to a local folder

2) Use a shortcut to copy the new version from the server

3) Use some type of auto-updater. This would automatically detect and copy the new version.

Tools to help deploy your Access Application (front end) on a LAN

Auto FE Updater

Application Starter

BTAB Development - Free access Code for deploying updates 

Easy Front-End AutoUpdater for large (or small) database environ

Front End Automatic Update to New Version

Using  VBScript

Peter Hibbs' VB6 app  Front End Updater Utility

Where do I update the tables and relationships?

The Data (Back end) is where you will find all the Tables and relationships. This is where you will make all the table design changes and define relationships.

Note: You can view the relationships from the Application (Front End). You can also view the table design from the Application (Front End). This is just for your convenience. You MUST open the Back End (data) to make design changes.

I like to use test Data (back end). I will make the changes to the back end and test the new Application (front end). When the new Application (front end) is deployed, you must also deploy the changes to the Back End (Data). This is where it gets more difficult. Unlike the Front End where you can just replace the file, you must apply the changes to the production back end. To update the back end, you need all the users out. You should have exclusive access to the database file. I usually use something to flag the front ends that the back end is offline and it disables the Application (front ends)

I highly recommend that you make a backup before applying your changes.

If you keep good documentation, you will have a record of the table changes you have made. You can manually apply the changes. Since I do a lot of remote updates to back ends, I use an updater application to make the changes to the back end (data)

Tools to help deploy back end updates

BTD Development - Free access Code for deploying updates

Peter Hibb's Back End Update Utility

Manage Remote Backend MS Access Database Programmatically With VB Code

DAO Programming Code Examples

Additional Resources

 For more information about splitting your database into an application / Front end and Data / Back End:, check out the following:

Significantly Improve the Performance of Microsoft Access Databases with Linked Tables

Splitting a access database, or how to run ms-access in a multi-user mode.

Splitting your Microsoft Access MDB into a front end and back end

Split your Access database into data and application

Utter Access Wiki article on Sharing your database

Have you split your database?