Part III: More-Advanced Access Techniques
768
Update Retry Interval
This option controls the period of time Access waits to retry a lock. Adjust this setting to accom-
modate the latency imposed by the network, slow computers, and other hardware constraints. The
default is 250 milliseconds (one-quarter of a second), the minimum is 0 milliseconds, and the
maximum is 1,000 milliseconds (1 second).Tip
An unnecessarily long Update Retry Interval setting and a high Number of Update Retries setting can result in
an uncomfortably long interval before the user sees a locking error message. In most cases, using the No Locks
option (see “Default Record Locking,” earlier) in conjunction with a brief Update Retry Interval setting and a
minimum Number of Update Retries setting is adequate.
Record-lock error handling
Even though you plan ahead and set Access’s default settings to a number you think will handle
record-locking problems in your application, you’re bound to encounter conflicts sometime. The
more users you have hitting your application, the better the chances that you’ll encounter a locking
conflict. You can capture the errors that Access throws, however, and use VBA to communicate
record-lock contention solutions to your users.In an effort to correct some performance problems and locking conflicts in earlier versions of
Access, Microsoft has developed Access so that it caches more data in memory and writes data to
disk only after the cache has been filled (unless specified in the engine’s Registry keys). Although
these enhancements do increase performance, they can make it harder to trap lock errors on spe-
cific records. Here’s an example: One person is changing data on Machine A. Another person is
editing data on Machine B. Both users change the same record, but the record changed is cached in
memory on Machine A along with several other records. Finally, Machine A runs out of cache
space and flushes the cache to disk. Because a locking violation has occurred (even though time
and records have long since passed the violation), Access flags an error. But because the record is
being written along with several others, figuring out exactly which record lock caused the error is
difficult.Another problem with Access’s caching behavior is latency. Because data is being stored in cache
on each machine, changes that may have occurred to data on each machine won’t be reflected to
other users until the cache is flushed to disk.In order to solve both of these problems, you must use explicit transactions in your procedures.
Explicit transactions enclose each transaction with Workspace.BeginTrans and Workspace.
CommitTrans (ADO transactions are managed by the Connection object).Caution
You can encounter locking problems within your transactions, so be sure to provide adequate error handling.