Locking Recordsets in Microsoft Access 2000
Effectively Handling Locking Conflicts
If a user has a page or record locked and another user tries to view data in the record, or on that page, no conflict occurs. On the other hand, if other users try to edit data on that same page, they get an error.
You won't always want Access's own error handling to take over when a locking conflict occurs. For example, rather than having Access display its generic error message indicating that a record is locked, you might want to display your own message and then try to lock the record a couple of additional times. To do something like this, you must learn to interpret each locking error generated by VBA, so you can make a decision about how to respond.
Locking conflicts happen in the following situations:
- A user tries to edit a record that's already locked.
- A record has changed or been deleted since the user first started to edit it.
These errors can occur whether you're editing bound data through a form or accessing the records through VBA code.
Errors with Pessimistic Locking
To begin the discussion of locking conflicts, take a look at the types of errors that occur when pessimistic locking is in place. With pessimistic locking, you generally need to code for the following errors:
- The current record is locked by another user. Usually, you can just wait a short period of time and then try the lock again.
- The record has been deleted since the recordset was retrieved. In this case, it's best to refresh the data.
| NOTE |
| The error numbers that occur differ based on the provider that is being used. The examples in this chapter therefore use sample error numbers. Refer to online help for trappable Microsoft Jet errors, ADO error codes, and ADO provider errors. As an alternative to the VBA error code, you can use the Errors collection of the Connection object to view properties of the error that occurred. |
Coding Around Pessimistic Locking Conflicts
It's fairly simple to write code to handle pessimistic locking conflicts. Your code should look like Listing 5.
Listing 5 - Handling Pessimistic Locking Errors
Sub PessimisticRS(strAuthorID As String)
On Error GoTo PessimisticRS_Err
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strCriteria As String
Dim intChoice As Integer
Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=sqloledb;" & _
"Data Source=(local);Initial Catalog=pubs;uid=sa;pwd="
cnn.Open
Set rst = New ADODB.Recordset
rst.ActiveConnection = cnn
rst.CursorType = adOpenKeyset
rst.LockType = adLockPessimistic 'Invoke Pessimistic Locking
rst.CursorLocation = adUseServer
rst.Open "Select * from Authors Where Au_ID = '" & _
strAuthorID & "'", _
Options:=adCmdText
rst!City = "Thousand Oaks" 'Lock occurs here
rst.Update 'Lock Released Here
Exit Sub
PessimisticRS_Err:
Select Case Err.Number
Case 3197
rst.Move 0
Resume
Case -2147217887
intChoice = MsgBox(Err.Description,
vbRetryCancel + vbCritical)
Select Case intChoice
Case vbRetry
Resume
Case Else
MsgBox "Couldn't Lock"
End Select
Case 3021
MsgBox "Record Has Been Deleted"
Case Else
MsgBox Err.Number & ": " & Err.Decription
End Select
End Sub
The error-handling code for this routine handles the errors that can happen with pessimistic locking. If a -2147217887 Record Is Locked error occurs, the user is asked whether she wants to try again. If she responds affirmatively, the edit process resumes; otherwise, the user is informed that the lock failed. If the record being edited has been deleted, an error 3021 occurs, and the user is informed that the record has been deleted. The situation looks like Listing 6 when transaction processing is involved.
Listing 6 - Handling Pessimistic Locking Errors in Transactions
Sub PessimisticRSTrans()
On Error GoTo PessimisticRSTrans_Err
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strCriteria As String
Dim intCounter As Integer
Dim intTry As Integer
Dim intChoice As Integer
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.ActiveConnection = cnn
rst.CursorType = adOpenKeyset
rst.CursorLocation = adUseServer
rst.LockType = adLockPessimistic 'Invoke Pessimistic Locking
rst.Open "tblCustomers", _
Options:=adCmdTable
cnn.BeginTrans
Do Until rst.EOF
rst!CompanyName = rst!CompanyName & 1 'Lock occurs here
rst.Update
rst.MoveNext
Loop
cnn.CommitTrans 'Lock released here
PessimisticRSTrans_Exit:
Exit Sub
PessimisticRSTrans_Err:
Select Case Err.Number
Case 3197
rst.Move 0
Resume
Case -2147217887
intCounter = intCounter + 1
If intCounter > 2 Then
intChoice = MsgBox(Err.Description, _
vbRetryCancel + vbCritical)
Select Case intChoice
Case vbRetry
intCounter = 1
Case vbCancel
Resume CantLock
End Select
End If
DoEvents
For intTry = 1 To 100: Next intTry
Resume
Case Else
MsgBox "Error: " & Err.Number & ": " & Err.Description
End Select
CantLock:
cnn.RollbackTrans
Exit Sub
End Sub
This code tries to lock the record. If it's unsuccessful (that is, an error -2147217887 is generated), it tries three times; then prompts the user for a response. If the user selects Retry, the process repeats. Otherwise, a rollback occurs and the subroutine is exited. If any other error occurs, the rollback is issued and none of the updates are accepted.
| NOTE |
| Listing 6 opens the tblCustomers table directly. This is for the example only. You should never open a base table directly because this downloads all the rows and their column properties from the server. Instead, you should limit the rows returned to only those rows you need to work with. Finally, when using a database server such as Microsoft SQL Server, the task of updating records is accomplished much more effectively using a stored procedure. |
Page 2 of 3
This article was originally published on March 23, 2001