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

Web 2.0-like User-Level Menus in Access
UI Builder for Microsoft Access offers user-level menus out of the box. Limit users to specific forms and reports in your database without implementing Access workgroup security. UI Builder is packed with features to enhance any multi-user database. Download a free trial today.

Microsoft Access Multi-User Applications

Multi-User Applications

Since its creation, Microsoft Access has always been a single user database application, but in recent years more and more businesses and individuals have started to use Microsoft Access databases as a storage medium especially on the web. This inevitably means that the access database will be accessed by more than one user at a time and since Access was not designed to handle this kind of activity, it will bring problems for any user or application. Two of the most common problems that come to mind are:

  • Data Corruption or Resource Contention – Having multiple users access and change data simultaneously often corrupts data. This often happens when one user changes data and another user overwrites this data immediately after it has been changed.
  • Performance and Speed - A poorly coded and designed application will generally run slower as it processes more data and have more users accessing it. Is what’s commonly known as the bottleneck effect.

There are several other complications that broadly fall within the above two areas and we will look at some of them here and also look at how to remedy them. Simultaneous access of resources generally means sharing resources between users and databases are perhaps the most shared of all. So how can we reduce the problems associated with sharing an Access database?

The first thing that you need to do when intending to use a Microsoft Access database in a shared environment is to put it in shared mode. You can do this by selecting Tools » Options, which opens the Options dialog box:

The Options dialog box

Click on the Advanced tab if it is not already showing and make your way to the Default Open Mode selection box and check the ‘shared’ option as above. This will then open all databases in shared mode by default. This means that you don’t have to go through this exercise every time you create/open a new database.

Dealing with Data Corruption

To better explain what I mean by data corruption lets take the following scenario: Say you have a contacts database with important data about your clients. Jane wants to update the details about a client called Graham. She opens up the database and starts updating the details. At the same time John opens up the database and removes Graham’s details from the database. Jane then updates Graham’s details that John just removed. Another scenario can be that both Jane and John update the same record at the same time. These kinds of conflicts or data corruption do occur in a multi-user access environment.

Luckily for us, Access provides us with some kind of solution. Microsoft Access offers a record locking facility that allows you to handle conflicts such as the ones we showed in the above scenarios. As far as I know, record locking is used only on databases on networks.

Record locking is used in two environments:

  • Access Interface
  • The ADO Recordset object.

Finally, in addition to record locking, Access also provides us with a way to see changes that has been made by a user to a record. Going back to our earlier scenario, when John opens up Graham’s record, he will be able to see the update that has been made by Jane. This happens because Access has a refresh interval setting which determines how long it takes for data changed by one user to be displayed in the session of a second user. The refresh interval can be set by selecting Tools » Options and then selecting the Advanced tab. There you will see the refresh interval option:

The image shows that the refresh interval is set to 60 seconds
The above image shows that the refresh interval is set to 60 seconds

In the next instalment of Multi-user Applications article, we will explore how to use the Microsoft Access Record Locking scheme in both the Access Interface as well as programmatically.

The Author

Leidago !Noabeb is a computer programmer based in Namibia. He has worked with both opensource and Microsoft technologies for over seven years and specializes in writing communications software. He has made many contributions to various online websites dedicated to web development. He can be reached at: leidago [at] googlemail.com