Heisenbug - Happens when running but not when debugging

[jhg6308]

> Error 3197: The Microsoft Access database engine stopped the process because you and another user are attempting to change the same data at the same time.

This is in a single-user situation, Windows 10, Access 365. The table in question is in the current project (not linked).
There is no possibility that any other process is attempting to access the same table.

I suspect a race condition in Access itself based on the following:

The code looks like this:

DBEngine.Workspaces(0).BeginTrans

Dim rs1 as DAO.RecordSet
Set rs1 = db.OpenRecordset("table", dbOpenTable)

Do While not rs1.EOF
    ...
    rs1.Edit
    rs1!field1 = some calculated value
    rs1!field2 = some other calculated value
    rs1.Update '******* ERROR THROWN HERE ********
    
    rs1.MoveNext
Loop
...
DBEngine.Workspaces(0).CommitTrans

' Error handling that does a rollback on failure is not shown

The error occurs when executing the Update on the SECOND row of the table.

I have found one situation in which the error does not occur:

1) Set a breakpoint ANYWHERE in the code BEFORE the Update statement
2) Execute the procedure, stopping at the breakpoint
3) Remove the breakpoint
4) Continue (F5)

After that specific sequence of operations the error no longer occurs until after I reset the runtime (Menu Run/Reset).

I've tried adding DoEvents in a few places but that doesn't seem to have an effect.

Is this a known bug? If so, what is the workaround?

[kd2017]

Just a shot in the dark but are you properly closing any objects you opened before the end of the procedure?

Does this happen if you close and reopen the db? How about a compact and repair?

[pbaldy]

Another shot, make sure there isn't a form open that's bound to the same table. That error can happen when a form is bound to a table and you edit data with code.

[Micron]

Edited my post.

[jhg6308]

Just a shot in the dark but are you properly closing any objects you opened before the end of the procedure?

Yes I am sure no objects are left open that shouldn't be open.

Does this happen if you close and reopen the db? How about a compact and repair?

After Compact&Repair without closing and reopening Access causes subsequent attempts to fail with "Disk I/O Error" in exactly the same place at the same table record (2). There's nothing in the System Event Log about disk errors, so I assume the Compact & Repair changed something and some other part of Access didn't get the message.

After that, closing and reopening restores the original behavior... that is the access conflict error message, which can be "cured" by setting a breakpoint in the procedure and hitting it once before proceeding.

One piece of information I forgot to include... I'm working inside a transaction - I'll update the code in my original question to show that.

[jhg6308]

Another shot, make sure there isn't a form open that's bound to the same table. That error can happen when a form is bound to a table and you edit data with code.

No forms are open... I'm testing the procedure by itself.

I've seen similar behavior before involving field accesses after MoveNext returning the previous row's data. Here's some code in my project that I wrote to work around that one:

' Key-value pairs from the DBConfig table
Public dbConfig As New Scripting.Dictionary
   ...
Public Sub initialize()
    ...
    ' Load the key-value pairs from DBConfig to a dictionary for global use
    dbConfig.RemoveAll
    Dim rs As DAO.Recordset, key As String, val As String
    Set rs = baseDb.OpenRecordset("DBConfig", dbOpenTable, dbReadOnly)
    Do While Not rs.EOF
        key = rs!key
        val = rs!val
        ' Don't rewrite the following with rs!key and rs!val...
        ' you'll get a duplicate key error (likely a bug in the library)
        dbConfig.Add key, val
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    ...
End Sub

[Minty]

Remove the transaction stuff.
I suspect Access can't perform the update because once you have an initial pending update, the records are locked by the outer transaction.

[jhg6308]

Remove the transaction stuff.
I suspect Access can't perform the update because once you have an initial pending update, the records are locked by the outer transaction.

That seems to bypass the error, but it turns what used to take < 1 sec into several minutes due to the implicit autocommit after every update. Besides, the design calls for all these updates to be atomic, so a transaction is required.

I'm not nesting transactions (only one level), and there's no reason transactions should cause this. Otherwise why have transactions at all?

The fact that it happens on the SECOND row means some part of the engine has the first row locked, and is confused about the lock when I try to update the second row.

And, it does not explain why a breakpoint prevents the error, which by definition should point to a bug in Access.

If transactions are this easy to break there's a much bigger problem.

I am forced to work in Access with a large legacy app. Otherwise I'd have long ago moved to a real database.

I guess the next step is to write all the updates to yet another temp table and then do a join update with the original table. Can access SQL express that?

[Minty]

It always makes more sense to use set based actions rather than RBAR https://www.red-gate.com/simple-talk...agonizing-row/

Looking at it further, I also question that you may be using two different DB workspace references e.g.

DBEngine.Workspaces(0).BeginTrans

And

Set rs1 = db.OpenRecordset("table", dbOpenTable)

Which might also be causing you a record locking problem.

[jhg6308]

Well...

The original goal was to generate a list of transactions, then update that list with running totals and balances.

I refactored the code to write the running totals and balances to a separate temp table, copying the primary key.

Now I can achieve my goal using a simple primary key join query instead of the transaction list.

And yes, I'm using two workspaces but not in the way you might think. I have a logging framework that needs to autocommit everything while some of the main routines need to be atomic/transactional. So I defined two workspaces and databases, baseDb/baseWorkspace (reference to currentDB and its workspace) and logDb/logWorkspace. But the only thing that references logDb/logWorkspace is the logging module, I've been EXTREMELY careful with that, and have verified that there are no references to logDb/logWorkspace outside the logging module.

I'm still left with the evidence that this is a bug: A single hit breakpoint before the error causes the error to not happen. A breakpoint should NEVER affect running code, with the possible exception of multiple threads and an underlying race condition. Access likely has many threads, but my VBA code does not (can VBA even do that?) so any race condition is Access' problem.

Anyway, I've wasted several days on this and now have a solution that works, preserves the transaction semantics, and finishes in < 1 second.

Thanks to all for the suggestions. If I had more time I'd try to create a simple example that reproduces the issue, but deadlines loom and I must move on.

[Minty]

Glad you have a solution.

Access is single threaded to the best of my knowledge, I have seen many examples of people trying to make it behave in a multithreaded fashion without success.
I would suspect that the breakpoint cause and effect is a bit of a red-herring, as I suspect the breakpoint will cause something to change in the scope of things, but I'm not nearly clever enough to tell you what that might be.

Good luck with the rest of your project.