DAO.Recordset.Update results in reckord lock

Question

I am trying to run the following code to loop around a recordset and do updates where neccessary.

I have a Microsoft Access database connected to a MySql backend. Whenever I run this code I get the following error:

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

The code is below:

Private Sub test()
    Dim rs As DAO.Recordset, rsCnt As Long, i As Long

    Set rs = CurrentDb.OpenRecordset("qryMyQuery", DB_OPEN_DYNASET)
    rs.MoveLast
    rsCnt = rs.RecordCount
    rs.MoveFirst
    For i = 1 To rsCnt
        rs.Edit
        rs!MyFieldInTable = "test"
        rs.Update
    Next i
End Sub

I thought the Access database might be corrupt so I pulled an earlier backup but it's doing the same thing which makes me think it's a MySql issue.

We use an identical piece of code on another version of this database linked to a different MySql table and it works fine.

Also, when I open the query the record-set is based on I can edit the data in the query without any issues.

Just to add, on the first loop, rs!MyFieldInTable is updated, then I get the error.

Answer 1

It does not appear that you are moving to another record in the recordset. Simply incrementing i doesn't move to the next record. A more traditional approach would be to iterate over the recordset without the need for your other variables (i and rsCnt).

Dim rs as DAO.Recordset
Set rs = CurrentDb.OpenRecordset("qryMyQuery", DB_OPEN_DYNASET)
rs.moveFirst
Do Until rs.EOF
    rs.Edit
    rs!FieldNameHere = "test"
    rs.Update
    rs.MoveNext
Loop

EDIT After a bit of searching I came across this thread which seems to be similar to your issue. At the bottom of the thread a suggestion is made to modify the ODBC settings for your MySQL DSN by selecting the "Advanced" tab and selecting the option to "Return Matching Rows". The post also says to drop the linked table and then re-link it to your Access database. I haven't used Access with MySQL in the past, so I have no idea whether this will work or not, so proceed with caution!

You may also try changing your recordset to use the dbOptimistic flag for the recordset locking option to see if that helps at all:

set rs = CurrentDB.OpenRecordSet("qryMyQuery", DB_OPEN_DYNASET, dbOptimistic)

Answer 2

Two things you can try. First, try adding the dbSeeChanges option when opening the recordset:

Dim rs as DAO.Recordset, db As DAO.Database
Set db = Currentdb
Set rs = db.OpenRecordset("qryMyQuery", dbOpenDynaset, dbSeeChanges)
Do Until rs.EOF
    rs.Edit
    rs!FieldNameHere = "test"
    rs.Update
    rs.MoveNext
Loop

The other option, as @HansUp suggested, is to use a SQL update statement instead of a dynamic recordset. The key there is to open the recordset as a snapshot, so that changes you make to the records do not affect the recordset itself.

Dim rs as DAO.Recordset, db As DAO.Database
Set db = Currentdb
Set rs = db.OpenRecordset("qryBatchPayments", dbOpenSnapshot)
Do Until rs.EOF
    db.Execute "UPDATE Payments " & _
               "SET DCReference='test' " & _
               "WHERE PaymentID=" & !PaymentID, dbFailOnError
    rs.MoveNext
Loop