Microsoft Access 2010 Bible

Chapter 21: Building Multiuser Applications

769

You’ve gotten a taste of what can happen in your multiuser applications: Performance can suffer,

and users can change each other’s data and encounter errors when a locking conflict occurs. The

good news is that you can plan for the errors, specifically by trapping for errors 3260, 3186, 3188,

and 3197. The next sections explain what each of these errors means, and give you some routines

to help you use them (instead of becoming their victim).

Error 3260: Couldn’t update; currently locked by user...

Remember pessimistic locking (see the “Default Record Locking” section, earlier in this chapter)?

Error 3260 most often occurs when pessimistic locking is enabled in an application. It occurs

when a user tries to lock a record for editing but another user already has the record locked

(because that person is editing it). You can choose to try again within your code, but one of the

problems inherent in pessimistic locking is that a user can hold a record for editing for an indefi-

nite period of time. This means that you have to provide a failure mechanism within your code just

in case you can’t obtain the lock after trying several times.

Before you waste time on edit procedures, make sure you can obtain a lock on the record you want

to edit. You can check to see if the record is locked by forcing the error (if there is one). All you

have to do is enable pessimistic locking and try to edit the desired record.

In an ADO application, pessimistic locking is implemented by setting the LockType parameter of

the Recordset object’s Open method:

rst.Open “Customers”, _

ActiveConnection:=conn, _

CursorType:=adOpenForwardOnly, _

LockType:=adLockReadOnly, _

Options:=adCmdTableDirect

The enumerated values for LockType are shown in Table 21.1.

TABLE 21.1

Enumerated Values for LockType

Enumerated Constant

Numeric

Value Description

adLockBatchOptimistic 4 Specifies optimistic batch updates. This value is required for

Batch Update mode.

adLockOptimistic 3 Optimistic locking on a record-by-record basis. The lock is

applied only when the Update method is called.

adLockPessimistic^2 Pessimistic locking on a record-by-record basis. The lock is

applied right after the user begins editing the record.

adLockReadOnly 1 The data is read-only.

adLockUnspecified –1 No lock type is specified.