Locking Recordsets in Microsoft Access 2000

March 23, 2001
By Alison Balter

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.