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.
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:
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:
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.
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:
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 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.