CODEWITHSUNDEEP
mysqlms-accessvba
JMK
JMK

Reputation: 28079

DAO.Recordset.Update results in reckord lock

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.

Upvotes: 3

Views: 8796

Answers (8)

Dustin
Dustin

Reputation: 21

I was having the same problem and my solution turned out to be the default value for BIT(1) fields. Access does not like these to be null. Make sure you use either 0 or 1 in mysql for these fields.

Upvotes: 2

AntonioFico
AntonioFico

Reputation: 1

I also had same problem; i solved them adding those to code using dao.recordset:

**rst.lockedits = true**
rst.edit
rst.fields(...).value = 1 / rst!... = 1
rst.update
**rst.lockedits = false**

this seems fix conflict between just opened data (such as in a form) and updating them with code.

Sorry for my bad english... i read a lot but i never had learn it! I'm just italian.

Upvotes: 0

Amy Patterson
Amy Patterson

Reputation: 1

My little helpful hint is, bits are very, very, very bad data types to use when linking SQL tables to Microsoft Access because only SQL Server understands what a bit is, Microsoft Access has a hard time interpreting what a bit is. Change any bit datatypes to int (integers) and relink your tables that should clear things up. Also, make sure your Booleans always contain a 1 or a 0 (not a yes/no or a true/flase) in your VBA code or your updates will fail to the linked SQL tables because Microsoft Access will try to update them with a True/False or a Yes/No and SQL will not like that.

Upvotes: 0

Lumis
Lumis

Reputation: 21629

I have discovered that if one tries to save data which are the same as the one already in the MySql record Access will display this kind of error. I've tried some suggestions from this thread but did not help.

The simple solution for this is to save a slightly diffrent data by using a manual time-stamp. Here is an example of heaving a sort order field and setting it to 10, 20, 30...

    i = 10
    timeStamp = Now()
    Do Until Employee.EOF
        Employee.Edit
        Employee!SortOrderDefault = i
        Employee!LastUpdated = timeStamp
        Employee.Update
        i = i + 10
        Employee.MoveNext
    Loop

I've tried automatic time-stamp in the MySql table but did not help when the new entry data is the same as the old one.

Upvotes: 0

mwolfe02
mwolfe02

Reputation: 24237

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

Upvotes: 2

HansUp
HansUp

Reputation: 97131

Try calling OpenRecordset from an object variable set to CurrentDb(), rather than directly from CurrentDb().

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

The reason for that suggestion is I've found operations on CurrentDb directly can throw an error about "block not set". But I don't get the error when using an object variable instead. And ISTR OpenRecordset was one such operation where this was an issue.

Also, my impression was your approach is a cumbersome way to accomplish the equivalent of:

UPDATE qryMyQuery SET FieldNameHere = "test";

However, I suspect the example is a proxy for a real world situation where the recordset approach is useful. Still that makes me wonder whether you would see the same or a different error when executing the UPDATE statement.

If you continue to have trouble with this, it may help to show us the SQL View for qryMyQuery.

Upvotes: 1

Tim Lentine
Tim Lentine

Reputation: 7862

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)

Upvotes: 4

XIVSolutions
XIVSolutions

Reputation: 4502

I don't have MySQL here to try this against, but it looks to me as if your code is not advancing the recordset after the rs.Update method is executed, so that you are trying to udate the same field in the fierst record.

Add this line after the rs.Update:

rs.MoveNext

Hope that helps.

Upvotes: 1

Related Questions