Microsoft Access 2010 Bible

Chapter 21: Building Multiuser Applications

773

because one of the two users is set to optimistic locking. If the user with optimistic locking tries to

save while the pessimist is still editing, the optimist is likely to get Error 3186. The same could hap-

pen if two optimistic updates were committed at the same time (although this situation is less likely).

Listing 21.5 shows how to test for locking status on a record that’s been obtained using optimistic

locking. Actually, there’s not much difference except that an edit takes place (rs.

Fields(“Lastname”) = UCase(rs.Fields(“LastName”))) using optimistic locking

(LockType:=adLockOptimistic). Contrary to Listing 21.3, the error occurs as the record is

updated instead of as it’s edited. Again, when the error occurs, the global error handler is called to

deal with the error.

LISTING 21.5

Optimistic Locking with ADO

Public Sub OptimisticLocking_ADO(ID As Long)

Dim cnn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim str As String

Dim lngTryCount As Long

On Error GoTo HandleError

str = “Provider= Provider=Microsoft.ACE.OLEDB.12.0;” _

& “Data Source=C:\Data\Northwind.mdb”

Set cnn = New ADODB.Connection

cnn.ConnectionString = str

cnn.Open

str = “SELECT * FROM Employees WHERE “ _

& “ EmployeeID = “ & ID

Set rs = New ADODB.Recordset

‘Open the recordset:

rs.Open “Customers”, _

ActiveConnection:=cnn, _

CursorType:=adOpenForwardOnly, _

LockType:=adLockOptimistic, _

Options:=adCmdTableDirect

‘Try to update a field:

rs.Fields(“LastName”) = UCase(rs.Fields(“LastName”))

ExitHere:

rs.Close

Set rs = Nothing

cnn.Close

Set cnn = Nothing

Exit Sub

HandleError:

‘Update Retry Count:

lngTryCount = lngTryCount + 1

continued