Locking Recordsets in Microsoft Access 2000
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-15Page 3 of 3
This article was originally published on March 23, 2001