<

Handle concurrent update conflicts in Access silently

Published on
23,374 Points
15,674 Views
7 Endorsements
Last Modified:
If several users try to update the same record simultaneously, an error pops up asking what to do. That's fine, users know what to do.  Contrary, if two processes driven from code do the same, there is no one to handle the situation, and it fails. That's bad.  Here is a method to avoid this.

Concurrent updates will fail - at least partly

This happens when two (or more) processes tries to update the same record:

 

  • The first starts editing
  • The second starts editing
  • One of them saves the edited record
  • The other cannot save because the record has changed  - which is true


Even worse, this also happens even if the two processes update different parts of the record, meaning that, in reality, you don't have a conflict.  To prevent this situation, different locking methods can be applied but that can be cumbersome to deal with. It would be much nicer with a self-healing method  that is easy and fast to apply.

 

If it fails, try again

This is the simple method users will turn to during daily work, but how could code be able to do that?  


First, here is a typical function that updates a record:

Public Sub UpdateTraditional()
    
    Dim db          As DAO.Database
    Dim rs          As DAO.Recordset
    Dim fd          As DAO.Field
    
    Dim SQL         As String
    Dim Criteria    As String
    Dim NewValue    As Boolean
    
    SQL = "Select * From " & TableName & ""
    Criteria = KeyName & " = " & CStr(KeyValue) & ""
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(SQL, dbOpenDynaset, dbSeeChanges)
    
    rs.FindFirst Criteria
    Set fd = rs.Fields(FieldName)
    NewValue = Not fd.Value
    
    rs.Edit
        fd.Value = NewValue
    rs.Update
    
    rs.Close
    
    Set fd = Nothing
    Set rs = Nothing
    Set db = Nothing
    
End Sub

The crucial part is the Edit and Update code lines where either can fail if another process is about to edit the record:

 

  • The Edit method may not be able to bring the record into edit  mode, thus the Update is doomed to fail
  • The Update will also fail if the record can't be saved


So what to do, is to test  and try  - and, if success, proceed, if fail, try again.



The smart way

To accomplish this, you can either build some extensive error trapping or - a bit smarter - use two tiny helper functions.


The first function checks the Edit mode:

' Function to replace the Edit method of a DAO.Recordset.
' To be used followed by GetUpdate to automatically handle
' concurrent updates.
'
' 2016-02-06. Gustav Brock, Cactus Data ApS, CPH.
'
Public Sub SetEdit(ByRef rs As DAO.Recordset)

    On Error GoTo Err_SetEdit

    ' Attempt to set rs into edit mode.
    Do While rs.EditMode <> dbEditInProgress
        rs.Edit
        If rs.EditMode = dbEditInProgress Then
            ' rs is ready for edit.
            Exit Do
        End If
    Loop

Exit_SetEdit:
    Exit Sub

Err_SetEdit:
    If DebugMode Then Debug.Print "    Edit", Timer, Err.Description
    If Err.Number = 3197 Then
        ' Concurrent edit.
        ' Continue in the loop.
        ' Will normally happen ONCE only for each call of SetEdit.
        Resume Next
    Else
        ' Other error, like deleted record.
        ' Pass error handling to the calling procedure.
        Resume Exit_SetEdit
    End If

End Sub

As you can see, if no other error has occurred, it tries over and over to bring the record into "Edit in Progress" and then exits.  This means, that when you exit from this function, the code can safely proceed editing the record.


The second function verifies the Update:

' Function to replace the Update method of a DAO.Recordset.
' To be used following SetEdit to automatically handle
' concurrent updates.
'
' 2016-01-31. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function GetUpdate(ByRef rs As DAO.Recordset) As Boolean

    On Error GoTo Err_GetUpdate
    
    ' Attempt to update rs and terminate edit mode.
    rs.Update
    
    GetUpdate = True

Exit_GetUpdate:
    Exit Function

Err_GetUpdate:
    If DebugMode Then Debug.Print "    Update", Timer, Err.Description
    ' Update failed.
    ' Cancel and return False.
    rs.CancelUpdate
    Resume Exit_GetUpdate

End Function

As you can see, it just tries to update and return True or False for success.  This means, that the calling code can either proceed  because the record is saved, or if not - very simple - has to try again.


So this is what we will do in a loop: Try to edit and update until success.  Now, recall the traditional function above for editing a record, and study the revised function that will use our two helper functions:

' Typical example for updating a field with concurrency handling
' using SetEdit and GetUpdate.
'
' 2016-01-31. Gustav Brock, Cactus Data ApS, CPH.
'
Public Sub UpdateConcurrent()
    
    Dim db          As DAO.Database
    Dim rs          As DAO.Recordset
    Dim fd          As DAO.Field
    
    Dim SQL         As String
    Dim Criteria    As String
    Dim NewValue    As Boolean
    
    SQL = "Select * From " & TableName & ""
    Criteria = KeyName & " = " & CStr(KeyValue) & ""
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(SQL, dbOpenDynaset, dbSeeChanges)
    
    rs.FindFirst Criteria
    Set fd = rs.Fields(FieldName)
    NewValue = Not fd.Value
    
    Do
        SetEdit rs
            fd.Value = NewValue
    Loop Until GetUpdate(rs)
    
    rs.Close
    
    Set fd = Nothing
    Set rs = Nothing
    Set db = Nothing
    
End Sub

Note that it effectively takes only one code line more to accomplish this - to form the Do ... Loop Until  loop.  This is an important aspect, because it means that the method is very easy and fast to apply to existing code.

 

Check it out

To check it out, use the ConcurrencyAwareTest function in the download. It connects to a standard Northwind  database and updates a record in the Products  table. Or modify and use with a table of your own:


' Function to run a sequence of updates at random intervals for a preset
' duration while handling any concurrency issue that may arise.
' Run the function concurrently in two or more instances of Microsoft Access.
'
' Output logs the updates and lists the errors encountered when an update
' collides with an ongoing update from (one of) the other instance(s).
'
' 2016-01-31. Gustav Brock, Cactus Data ApS, CPH.
'
Public Sub ConcurrencyAwareTest()
    
    Dim db          As DAO.Database
    Dim rs          As DAO.Recordset
    Dim fd          As DAO.Field
    
    Dim StopTime    As Single
    Dim Delay       As Single
    Dim Attempts    As Long
    Dim LoopStart   As Single
    Dim LoopEnd     As Single
    Dim Loops       As Long
    
    Dim SQL         As String
    Dim Criteria    As String
    Dim NewValue    As Boolean
    
    SQL = "Select * From " & TableName & ""
    Criteria = KeyName & " = " & CStr(KeyValue) & ""
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(SQL, dbOpenDynaset, dbSeeChanges)
    
    rs.FindFirst Criteria
    Set fd = rs.Fields(FieldName)
    
    ' Set time for the test to stop.
    StopTime = Timer + Duration
    ' Let SetEdit and GetUpdate print debug information.
    DebugMode = True
    
    ' At random intervals, call updates of the field until StopTime is reached.
    While Timer < StopTime
        
        ' Postpone the next update.
        Delay = Timer + Rnd / 100
        While Timer < Delay
            DoEvents
        Wend
        Loops = Loops + 1
        LoopStart = Timer
        Debug.Print Loops, LoopStart
        
        ' Perform update.
        NewValue = Not fd.Value
        Do
            ' Count the attempts to update in this loop.
            Attempts = Attempts + 1
            ' Attempt edit and update until success.
            SetEdit rs
                fd.Value = NewValue
        Loop Until GetUpdate(rs)
        
        LoopEnd = Timer
        ' Print loop duration in milliseconds and edit attempts.
        Debug.Print , LoopEnd, Int(1000 * (LoopEnd - LoopStart)), Attempts
        Attempts = 0
        
    Wend
    rs.Close
    
    DebugMode = False
    Set fd = Nothing
    Set rs = Nothing
    Set db = Nothing
    
End Sub


Create a copy of the accdb  file so you can have two instances open. Now run the function in both instances, and you will get an output like this (snipped):


First process
-----------------------
 7             54449.09 
    Update     54449.38     Microsoft Access has stopped the process ...
    Edit       54449.48     Microsoft Access has stopped the process ...
    Edit       54449.54     Microsoft Access has stopped the process ...
               54450.02      929           2 

Second process
-----------------------
 1             54448.38 
    Update     54448.57     Microsoft Access has stopped the process ...
    Edit       54448.69     Microsoft Access has stopped the process ...
    Edit       54449.07     Microsoft Access has stopped the process ...
               54449.29      914           2 
 2             54449.3 
               54449.54      238           1 
 3             54449.56 
    Update     54450.05     Microsoft Access has stopped the process ...
    Edit       54450.18     Microsoft Access has stopped the process ...
               54450.39      828           2 
 4             54450.4 
               54450.64      234           1 

Note, that the first process tries to update at 49.09 but can't. First the update fails, then two edit attempts. Finally, after the save at 49.29 of the second process, the first process manages to save at 50.02. And so on.


For fun, the download also contains a function ConcurrencyNotAwareTest that does not contain the concurrency check. It will, of course, used in the same way as above, fail sooner or later.


All you need to apply this technique to your own application, is the code module with the two functions. It is also contained in the Concurrency.bas file as a separate download:

 

7
Comment
1 Comment
LVL 54

Author Comment

by:Gustav Brock
Updated to version 1.0.2 with extended error handling in SetEdit to prevent an endless loop in case of other errors than a concurrent edit.

/gustav
0

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Join & Write a Comment

This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month