Locking Recordsets in Microsoft Access 2000

Friday Mar 23rd 2001 by Alison Balter
Share:

Don't get locked out! Have you ever had trouble knowing what is and isn't locked when working with a Microsoft Access program? 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. This article shows you how.

Recordset Locking

Recordset locking locks pages of data found in a recordset. By using recordset locking, you can control when and for how long the data is locked. This is different from locking data through bound forms, which gives you little control over the specifics of the locking process.

When you're traversing through a recordset, editing and updating data, locking occurs regardless of whether you intervene, so you must understand when the locking occurs and whether you need to step in to intercept the default behavior. If you do nothing, a record, or possibly an entire page of records, will be locked each time you begin editing data from your VBA code. This record page is 4096 bytes (4K) and surrounds the record being edited. If an OLE object is found in the record being edited, it isn't locked with the record because it occupies its own space.

Pessimistic Locking

VBA lets you determine when and for how long a page is locked. The default behavior is called pessimistic locking, which means that the record or page is locked when the first field is updated. Listing 1 illustrates this process.

Listing 1 - Utilizing Pessimistic Locking

Sub PessimisticLock(strAuthorID As String)
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strCriteria As String

    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
End Sub
NOTE
Not all database providers support all lock types. To determine the functionality available for a particular Recordset object, use the Supports method with adUpdate and adUpdateBatch. Furthermore, the adLockPessimistic setting is not supported when the CursorLocation property is set to adUseClient. Whether the provider does not support the locking type, or the cursor location does not support the locking type, no error results. Instead, the closest available locking type is used.

In this scenario, although the lock occurs for a very short period of time, it's actually being issued when the data is first edited; then released at the update.

The advantage of this method of locking is that you can make sure no changes are made to the data between the time the edit process begins and the time the Update method is issued. Furthermore, when the edit process begins successfully, you are ensured write access to the record. The disadvantage is that the time between the edit and the update might force the lock to persist for a significant period of time, locking other users out of not only that record, but if page locking is used, the entire page of records the edited record is in.

This phenomenon is exacerbated when transaction processing is invoked. Basically, transaction processing ensures that when you make multiple changes to data, all changes are made successfully or no changes occur. Listing 2 illustrates how pessimistic record locking affects transaction processing:

Listing 2 - Pessimistic Record Locking and Its Effect on Transaction Processing

Sub PessimisticTrans(strOldCity As String, strNewCity As String)
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strCriteria As String

    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 City = '" & 
                     strOldCity & "'", _
        Options:=adCmdText

    cnn.BeginTrans
    Do Until rst.EOF
        rst!City = strNewCity 'Lock occurs here
        rst.Update
        rst.MoveNext
    Loop
    cnn.CommitTrans  'Lock released here
End Sub

Here you can see that the lock is in place from when the city of the very first record is edited until the CommitTrans is issued. This means that no one can update any records, or possibly pages of data, involving the edited records until the CommitTrans is issued. This can take a prohibitive amount of time during a long process.

Optimistic Locking

Optimistic locking delays the time at which the record is locked. The lock is issued upon update rather than when the first field is edited. The code is shown in Listing 3.

Listing 3 - Utilizing Optimistic Locking

Sub OptimisticLock(strAuthorID As String)
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strCriteria As String

    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
End Sub

As you can see, the lock doesn't happen until the Update method is issued. The advantage of this method is that the page, or record, is locked very briefly. However, the disadvantage occurs when two users grab the record for editing at the same time. When one user tries to update, no error occurs. When the other user tries to update, she gets an error indicating that the data has changed since her edit was first issued.

Optimistic locking with transaction handling isn't much different from pessimistic locking. As the code reaches the Update method for each record, the page containing that record is locked, and it remains locked until the transaction is committed. The code appears in Listing 4.

Listing 4 - Optimistic Record Locking and Its Effect on Transaction Processing

Sub OptimisticTrans(strOldCity As String, strNewCity As String)
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strCriteria As String

    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 Batch Locking
    rst.CursorLocation = adUseServer
    rst.Open "SELECT * FROM Authors WHERE City = '" & 
                                   strOldCity & "'", _
        Options:=adCmdText

    cnn.BeginTrans
    Do Until rst.EOF
        rst!City = strNewCity
        rst.Update 'Lock occurs here
        rst.MoveNext
    Loop
    cnn.CommitTrans  'Lock released here
End Sub
NOTE
The constant adLockBatchOptimistic is used when batch updates, rather than immediate updates, are desired. The updates do not occur until the UpdateBatch method of the Recordset object is used. In the case of batch optimistic locking, the records are not locked until the UpdateBatch method is issued.

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.

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
Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved