 |
How stable is Access?
Microsoft Access can be a highly stable platform if implemented properly.
Most issues with stability are the result of system software issues
for which patches are available, but many users have not implemented
the latest patches.
We have attempted here to provide a list of the most important issues
that affect the systems we develop, both as a guide for product installers
for internal support resources at our existing clients, and as a useful
reference for other Access developers. We do not claim that these will
address every issue, but this section will address those issues that
are most likely to occur when using Access in a multi-user environment.
Much of this is sourced from a very useful
Microsoft Knowledge-Base article , with our own experiences added
into the mix.
You may need to register a Microsoft .net passport to read MS Knowledge Base articles.
Step 1: Patch, Patch, Patch!
Get the latest patches for your operating system and Office suite.
We cannot emphasis this enough, as many issues that only affect particular
combinations of products. If you have a well-used combination (i.e.
the latest of everything), you can be fairly sure that many other companies
and expert testers have done the testing for you!
Click here
for the starting point for Microsoft patch downloads.
Step 2: Patch some more!
As well as operating system and Office patches, there is a particular
patch that is specific to Access that really matters! The Microsoft
JET Engine is an important component of connecting and using any Microsoft
Access database. It is delivered with Office, but is not usually patched
with the updates for various reasons. You should always apply the latest
JET patch.
Click here
for the "how-to" blurb and download from Microsoft site.
Step 3: Opportunism Costs!
You have probably never heard of "Opportunistic Locking". It's been a
feature of the Microsoft client/server model since Windows 2000/XP onwards.
It is intended to provide a more fault-tolerant way for users to share
documents, and avoid the chance of somebody who is not really going
to change a document from unintentionally stopping somebody else from
make changes. In effect, it enables a floating lock on a shared file,
with the operating system deciding who had the "real" lock only when
it comes down to a conflict.
Unfortunately, this is a disaster for Access, where many users are all
expected to be making changes to the one database (MDB) file under normal
conditions. The most common symptom of this problem is a "somebody else
has exclusive use of the database" message, even though the database
is set to be opened for sharing. PCs may just hang or crash as well,
depending on a range of factors, and this can corrupt the MDB file.
The solution is to turn Opportunistic Locking OFF, by setting an option
in the registry of either the client or the server. Since it only takes
one server/client pair with the option ON to lock up the database, we
always recommend turning it OFF at the server end to be safe. If this
is not possible, it must be turned OFF on every client PC that might
open the database file.
Remember that it is always ON by default, so new PCs and servers, upgrading
your operating system, restorations of PCs from backup images, etc may
turn it back ON by accident.
Click here
for instructions on how to turn Opportunistic Locking off.
Step 4: Standardise Software Versions
The other major source of instability is mixing versions of Access
among clients using the same database. It is possible to run mixed versions
with stability, but it's a risky path that you don't have to take! As
a general rule, you should use exactly the same version of Access on
every PC on your network (including the latest service patch for that
release).
This is another case of avoiding having a rare untested combination,
and it applies equally to hardware, operating systems and other software
too. A uniform environment creates a combination that many people have
tried and tested before you. Software vendors have fixed any problems
found and delivered the fixes in the service patches. It is thus inherently
safer than running your own randomly derived combination that nobody
suspected would ever be run, and has not been tested or de-bugged!
If all this talk of instability concerns you (and it should), then this
section has been useful. If you move to a more uniform environment,
you may find that many other apparently random or "hard to find" issues
with your computer systems suddenly disappear as well.
Step 5: Design for Stability
In general, we design all of our applications to avoid known issues,
and also design with safety in mind. We design our applications to remain
at "arms length" to the database at all times. This means that they
never rely on the database providing a continuing connection between
the front-end and back-end while waiting for user actions.
The process model follows the following pattern:
User Event -> Connect -> Transfer Data –> Disconnect -> Wait for
More Events
This model provides a fast and reliable application in multi-user environments.
It also suits the ODBC interface model which is used to connect Access
to server-based databases, particularly when used across a slow wide
area network.
None of this will matter to our clients, since our applications and
products are all designed this way. There are many successful, large-scale,
mission critical applications around the world that use Access as a
front-end, and many of those also use Access as the back-end too.
The Access form wizards and default forms produce highly connection-reliant
applications, exactly the opposite to the model we use. This is one
reason why systems written by inexperienced developers have so many
issues with stability and performance.
The other sections all direct you to a solution to the issues discussed.
The solution to this one is to always get an experienced developer to
develop your software... like us!
Step 6: Ask an expert
If this section has prompted any questions, we invite you to contact
us and talk about your experiences and issues. Even if it's out of our
scope to help, we can almost certainly find someone who can help.
Click here to find out about
our customised applications.
|
|
