Microsoft Access 2010 Bible

Chapter 21: Building Multiuser Applications

771

Set cnn = Nothing

Exit Sub

HandleError:

‘Update Retry Count:

lngTryCount = lngTryCount + 1

‘Call ErrorRoutine, passing

‘in the number of retries:

Select Case ErrorRoutine(lngTryCount)

Case 3

‘Try again at the same statement

‘that caused the error:

Resume

Case 4

MsgBox “Edit Canceled”

GoTo ExitHere

End Select

End Function

Listing 21.4 performs the same operation as Listing 21.3, but this time on a local Employees

table using DAO syntax and a somewhat different technique for referencing the LastName field in

the recordset.

LISTING 21.4

Pessimistic Locking in DAO

Public Sub PessimisticLocking_DAO(ID As Long)

Dim db As DAO.Database

Dim rs As DAO.Recordset

Dim strSQL As String

Dim lngTryCount As Long

On Error GoTo HandleError

Set db = CurrentDb

strSQL = “SELECT * FROM Employees “ _

& “WHERE Employees!EmployeeID = “ & ID

Set rs = db.OpenRecordset(strSQL)

‘Set record locking to Pessimistic Locking:

rs.LockEdits = True

rs.Edit

rs!LastName = UCase(rs!LastName)

rs.Update

ExitHere:

rs.Close

Exit Sub

continued