Checking for Errors with Page Locking

When using page locks, your code must check to see if the attempted lock succeeded before proceeding. As with the previous examples, use the four-step process of turning off error handling, attempting the operation that will initiate a lock, checking for errors, and finally, turning on error handling.

The following table describes the most common page-locking multiuser errors your code will encounter.

See Also For more information about Microsoft Jet error numbers, see Appendix D, “Error Reference.”

In the previous pessimistic locking example, the code checked to see if an error occurred and reacted regardless of the type of error. This example can be made more versatile by actually checking the error that occurs and responding to it. The following function modifies the pessimistic locking example to include better error handling:

Sub EditARecord()
	Const conMultiUserEdit As Integer = 3197
	Const conRecordLocked As Integer = 3260
	Dim dbs As Database, rst As Recordset
	Dim intLockCount As Integer, intRndCount As Integer
	Dim intChoice As Integer, intX As Integer
	Dim strDbPath As String
  
	On Error GoTo 0
	strDbPath = "C:\JetBook\Samples\NorthwindTables.mdb"
	' Open the database in shared mode.
	Set dbs = OpenDatabase(strDbPath, False)
  
	' Open the Orders table in shared mode.
	Set rst = dbs.OpenRecordset("Orders", dbOpenDynaset, dbSeeChanges, dbPessimistic)
  
	With rst
		' Setting LockEdits to True tells Jet to use
		' pessimistic locking.
		.LockEdits = True
		.FindFirst "[OrderID]=10565"
	
		' Try to edit the row. This will cause a lock attempt.
		On Error GoTo Err_EditARecord
		' If record is locked, edit will fail.
		.Edit
	
		' Make changes and commit.
		![ShipAddress] = "New Address 3"
		.Update
	End With

Exit_EditARecord:
	On Error Resume Next
	rst.Close
	dbs.Close
	Exit Sub
  
Err_EditARecord:
	Select Case Err.Number
		' Data has changed since last read.
		Case conMultiUserEdit
			Resume
		Case conRecordLocked
			' Row is locked.
			intLockCount = intLockCount + 1
			' Tried to get the lock twice already.
			If intLockCount > 2 Then
				' Allow user to choose Cancel/Retry.
				intChoice = MsgBox(Err.Description & " Retry?", _
					vbYesNo + vbQuestion)
				If intChoice = vbYes Then
					intLockCount = 1
				Else
					Resume FailedEdit
				End If
			End If
		
			' Yield to Windows.
			DoEvents
			' Delay a short random interval,
			' longer each time the lock fails.
			intRndCount = intLockCount ^ 2 * Int(Rnd * 3000 + 1000)
			For intX = 1 To intRndCount: Next intX
			' Try the edit again.
			Resume
		Case Else
		   ' Unanticipated error.
			MsgBox ("Error " & Err.Number & ": " & Err.Description)
			Resume FailedEdit
	End Select
  
FailedEdit: ' Begin contingency procedure if edit fails.
		MsgBox "This row could not be edited. Please try again later."
		GoTo Exit_EditARecord
End Sub

This code works by moving the program’s execution to the Err_EditARecord section when an error occurs. The code in the Err_EditARecord section checks the specific error message and tries to correct the problem. It also allows the user to retry or cancel the operation. Note that it specifies a random interval for retrying the operation. This is an important technique for making sure two users vying for the same record don’t end up in a deadlock situation where code is trying to lock the record at the same time. By introducing a random element into the timing loop, you can minimize the chances of a deadlock.