dcsimg
December 4, 2016
Hot Topics:

Locking Recordsets in Microsoft Access 2000

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

Errors with Optimistic Locking

Now that you have seen what happens when a conflict occurs with pessimistic locking, see what happens when optimistic locking is in place. These are the two most common error codes generated by locking conflicts when optimistic locking is in place:

  • An error occurs when the Update method is used to save a locked record or a record on a locked page. This error can occur when optimistic locking is used and a user tries to update a record on the same page as a record that's locked by another machine. You can usually just wait a short period of time and then try the lock again.
  • An error occurs with optimistic locking when someone else has updated a record in the time since you first started viewing it.

Coding Around Optimistic Locking Conflicts

Remember that with optimistic locking, VBA tries to lock the page when the Update method is issued. There's a chance that a Data Has Changed error could occur. This needs to be handled in your code, so modify the preceding subroutine for optimistic locking with the code in Listing 7.

Listing 7 - Handling Optimistic Locking Errors

Sub OptimisticRS(strAuthorID)
    On Error GoTo OptimisticRS_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 = adLockOptimistic 'Invoke Optimistic Locking
    rst.CursorLocation = adUseServer
    rst.Open "Select * From Authors Where Au_ID = '" & _
         strAuthorID & "'", _
         Options:=adCmdText

    rst!City = "Thousand Oaks"
    rst.Update 'Lock occurs and is Released Here

OptimisticRS_Exit:
    Exit Sub

OptimisticRS_Err:
  Select Case Err.Number
   Case -2147217885
     If rst.EditMode = adEditInProgress Then  'Data has Changed
       MsgBox "Another User has Edited Record Since You Began " & _
          "Modifying It"
     End If
   Case -2147217871   'Locked or ODBC Timeout
      intChoice = MsgBox(Err.Description, vbRetryCancel + vbCritical)
          Select Case intChoice
             Case vbRetry
                 Resume
             Case vbCancel
                 MsgBox "Update Cancelled"
          End Select
   Case Else
      MsgBox "Error: " & Err.Number & ": " & Err.Description
   End Select
   Resume OptimisticRS_Exit
End Sub

As with pessimistic error handling, this routine traps for all potential errors that can occur with optimistic locking. In the case of a Data Has Changed conflict, the user is warned of the problem. In the case of a locking conflict, the user is asked whether she wants to try again. Listing 8 shows what it looks like with transaction processing involved.

Listing 8 - Handling Optimistic Locking Errors in Transactions

Sub OptimisticRSTrans()
    On Error GoTo OptimisticRSTrans_Err
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strCriteria As String
    Dim intChoice As Integer
    Dim boolInTrans As Boolean

    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 = adLockOptimistic 'Invoke Optimistic Locking
    rst.CursorLocation = adUseServer
    rst.Open "tblCustomers", _
        Options:=adCmdTable

    cnn.BeginTrans
    boolInTrans = True
    Do Until rst.EOF
        rst!CompanyName = rst!CompanyName & 1 'Lock occurs here
        rst.Update
        rst.MoveNext
    Loop
    cnn.CommitTrans  'Lock released here
    Exit Sub

OptimisticRSTrans_Err:
    Select Case Err.Number
        Case -2147217885 'Data has Changed
            If rst.EditMode = adEditInProgress Then
                MsgBox "Another User has Edited Record" & _
                " Since You Began " & _ 
                 "Modifying It"
            End If
        Case -2147217871   'Locked or ODBC Timeout
            intChoice = MsgBox(Err.Description, _
                vbRetryCancel + vbCritical)
                Select Case intChoice
                    Case vbRetry
                        Resume
                    Case vbCancel
                        MsgBox "Update Cancelled"
                End Select
        Case Else
          MsgBox "Error: " & Err.Number & ": " & Err.Description
    End Select

    If boolInTrans Then
        cnn.RollbackTrans
    End If

    Exit Sub
End Sub

If a Data Has Changed conflict occurs, the entire processing loop is canceled (a rollback occurs). If a locking error occurs, the lock is retried several times. If it's still unsuccessful, the entire transaction is rolled back.

Testing a Record for Locking Status

Often, you want to determine the locking status of a record before you attempt an operation with it. By utilizing pessimistic locking and trying to modify the record, you can determine whether the current row is locked. The code looks like Listing 9.

Listing 9 - Determining Whether a Record Is Locked Before Editing It

Sub TestLocking()

    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim boolLocked As Boolean

    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 "Authors", Options:=adCmdTable

    boolLocked = IsItLocked(rst)
    MsgBox boolLocked

End Sub

Function IsItLocked(rstAny As ADODB.Recordset) As Boolean
    On Error GoTo IsItLocked_Err
    IsItLocked = False

    With rstAny
        .Update
    End With
    Exit Function

IsItLocked_Err:
    If Err = -2147467259 Then
        IsItLocked = True
        Exit Function
    End If
End Function

The TestLocking routine sends its recordset to the IsItLocked() function, which receives the recordset as a parameter. It then issues an Update method on the recordset. If an error occurs, the record is locked. The error handler sets the return value for the function to True.

In conclusion

VBA offers several alternative locking strategies for the developer, ranging from locking the entire database to locking one record at a time. In addition, VBA lets you control how long data will be locked. You use this feature through the techniques of optimistic and pessimistic locking, as well as deciding on page locking versus record locking. The developer must select which combination of strategies should be used in each particular application. The decision about which method to use is influenced by many factors, including the volume of traffic on the network and the importance of making sure collisions never happen.

About the Author

Alison Balter is the author of Mastering Microsoft Access 2000 Development(Click to buy) a book published by Sams Publishing. This article is based on information from her book.

Alison Balter is the president of InfoTechnology Partners, Inc., a Microsoft Solutions Partner. Her training videos for Keystone Learning Systems are well-known in the Access community. Alison is a regular speaker at conferences, is an author and speaker for Advisor Media, and has also written a number of Microsoft Access books.

© copyright 2001 by Sams Publishing. All Rights Reserved.

Alison Balter's Mastering Access 2000, 0-672-31484-3, 17 17-15



Page 3 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