Part III: More-Advanced Access Techniques
770
In a DAO environment, use the LockEdits property (a Boolean) of a Recordset object to spec-
ify the default record-locking behavior of a recordset. Set LockEdits to True to specify pessi-
mistic locking:
MyRecordset.LockEdits = True
Set LockEdits to False for optimistic locking:
MyRecordset.LockEdits = False
Listing 21.3 creates a recordset based on the Northwind Traders Employees table and an
EmployeeID passed to the function as an argument. It then sets record locking to pessimistic
locking and tries to lock the record for editing. If the edit fails, the error handler (see the “A function
to handle lock errors” section, later in this chapter) uses an empty loop to wait a few seconds before
trying the edit again. After four tries, the edit fails and the user is notifi ed of the failure.
LISTING 21.3
Pessimistic Locking in ADO
Public Sub PessimisticLocking_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
‘Must use the Jet OLEDB provider
‘when opening an Access ACCDB file:
str = “Provider=Microsoft.ACE.OLEDB.12.0;” _
& “Data Source=C:\Data\Northwind.accdb”
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 str, _
ActiveConnection:=cnn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockPessimistic, _
Options:=adCmdTableDirect
‘Try to update a field:
rs.Fields(“LastName”) = UCase(rs.Fields(“LastName”))
ExitHere:
rs.Close
Set rs = Nothing
cnn.Close
