databasedev.co.uk - database solutions and downloads for microsoft access

Optimizing Multi-user Microsoft Access Applications

In the previous article we looked at some of the problems that Microsoft Access multi-user applications have. We also looked at how to prevent and /or solve these problems. In this article we will look at how to make multi-user applications perform better by suggesting ways in which they can be made more efficient. A very common and in some cases unavoidable problem that multi-user applications experience, is that they become very slow. This is mainly due to increasing network traffic as more and more users make use of the same resources.

Most Access multi-user applications involve a database that is accessed simultaneously by different users and applications. The data contained in this database can be described as dynamic meaning that in a networked environment the data does change as users add new records or edit them. How ever, the components and objects of your Access application – forms, reports, queries etc - are static. In majority of cases these database objects are stored in a central database and each time a user accesses this application they receive these static objects together with the dynamic data. Receiving and sending the same static data over the network again and again will of course affect the performance of the application negatively especially as more and more users make use of your application. An obvious solution to this scenario would be to use the network to only send dynamic data and at the same time enable users to load the forms, queries etc. locally.

Microsoft Access provides us with exactly that capability. It has a utility called Database Splitter that as the name implies splits a database into a front end that resides on the user’s desktop and a back end that resides on a single machine and is shared by all users of the application. The utility is available from Access 97 and later, and can be accessed by selecting Tools | Database Utilities | Database Splitter. It is advisable to make a backup of your original database, as a precaution, before splitting it.

Fig 1. The Database Splitter dialog box

Fig 2. Creating the Back-end Database

After the database has been split, Microsoft Access will confirm it with a dialog box stating that your database has been successfully split.

Fig 3. Confirmation of Database Split

This simply means that you will now have a backend and front end copies of your database. The two copies of your database will be distinct in the sense that all tables will be saved to the backend copy while most other objects such as forms, reports, macros etc will be placed in the front end copy. Note also that the front end copy of your database will not have any tables in it; this is indicated by the little black arrow icon that appears next to the table names in the tables window. They will have links to the tables that is now stored in the backend copy of your database:

Fig 4. Displaying the database Front-End

Once the database is split, you can make the front end copy of your database available to as many users as possible and move the backend copy to a central location such as a server. The problem with moving these copies around is that you need to update the link between the back and front ends, so that the links in the front end can locate the tables that are stored in the backend.

So how would you update the links?

Well Microsoft Access provides us with the Linked Table Manager, which as the name implies, manages links between tables. To access the linked table manager, simply right click on the linked table and select Linked Table Manager from the list:

Fig 5. Linked Table Manager menu

Once you click on the Linked Table Manager option, you should get the following window:

Fig 6. The Linked table manager

The dialog box lists the linked tables in the current database and shows the location of the physical tables to which they point. You can select the links that you'd like to update, and then click the OK button. Access will then display a File Open dialog box that prompts you for the new location of the back-end database file.

Splitting databases as above is a fantastic way of solving the network ‘bottleneck’ problem the only drawbacks is that every time you make a change to any of the objects contained in the front end part of your application you need to redistribute that copy to all your users. If you only have one or two users, this is not a problem; if you have a few hundred users then it just might become one!