dcsimg
December 9, 2016
Hot Topics:

Locking Recordsets in Microsoft Access 2000

  • March 23, 2001
  • By Alison Balter
  • Send Email »
  • More Articles »

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



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Sitemap | Contact Us

Thanks for your registration, follow us on our social networks to keep up-to-date
Rocket Fuel