Sharing an Access Database

Often, when I go and visit a Company I find that they are not using their database effectively. Sometimes only one person can use it at a time. Other times the users will tell me that although several people can use it, only one person can save anything and that’s the first person to use it.

 

One Company I visited recently had a timetable on display with a list of users and times during the day when each user could use the database.

 

Sometimes, people have private databases and if there is only one user, the database works perfectly.

But Databases are designed to be shared and an Access Database needs to be configured in a particular way to make sharing work properly. Typically Access can have over 200 users and up to 30 users working on it at the same time.

The trick is to separate the Database into two parts, the Front End which stores Forms, Reports and Queries, and the Back End which holds the Database Tables.

The Forms reports and Queries take the raw data from the Tables and process it.

It is only the Tables that need to be shared.

The idea is that every user has their own Front End, but there is only one Back End. All users have a linked database where their individual Front Ends are linked to a common Back End.

 

Several Computers Connected to a Back-End Server

Split Database – Diagram

 

 

This is called ‘Splitting a Database’. The facility to do this is built into Access.

 

These are the steps you need to go through:

 

1. Create a Shared Folder where you can store all the Access Tables. This should be a location where all users have Read/Write access.  Ideally it should be located in an area that is regularly backed up.
It makes life a lot easier if the shared location has the same path name for all users. You may wish to ‘Map Network Drive’ to do this.

Ribbon Display of Access

Select Move Data – Access Database

2. Open the Access Database. For Access 2010, select Database Tools->Move Data->Access Database. The Wizard will now take you through the steps to split your Database into a Front End and a Back End.  The Backend will be stored in your shared location. If your database was called abc.accdb the back end will be called abc_be.accdb. The name of the Front End is unchanged.

Picture of the Splitting Wizard

The Access Wizard copies all your tables into another empty Database

3. You can now make a copy of the Front End and distribute this to all your users. Provided they can all access the shared location via the same path, the database will work as before. Make sure you remove any old links to the unsplit database.

Linked Tables View

You should now be able to share the database so that several user can work on it at the same time. To show it is split, the main database shows little arrows showing that the tables for the Front End are stored on the Back End Database and linked to it.

If you would like some more help with splitting Databases, please give us a ring.