Handle concurrent update conflicts in Access silently

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-01-31. 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
    ' Continue in the loop.
    ' Will normally happen ONCE only for each call of SetEdit.
    Resume Next

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:

Access application with full code and test: Ctest-V1.0.2.zip
Access module: Concurrency.bas
Northwind database (for SQL Server 2000+): https://www.microsoft.com/en-us/download/details.aspx?id=23654
Source at GitHub: https://github.com/GustavBrock/VBA.ConcurrencyUpdates.git

I hope you found this article useful. You are encouraged to ask questions, report any bugs or make any other comments about it below.

Note: If you need further "Support" about this topic, please consider using the Ask a Question feature of Experts Exchange. I monitor questions asked and would be pleased to provide any additional support required in questions asked in this manner, along with other EE experts.

Please do not forget to press the "Thumbs Up" button if you think this article was helpful and valuable for EE members.

Handle concurrent update conflicts in Access silently

Concurrent updates will fail - at least partly

If it fails, try again

The smart way

Check it out

Comments (1)