Resources

Online Shop

Info

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:

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