vba DAO.Recordset.Update 导致记录锁定

DAO.Recordset.Update results in reckord lock

提问by JMK

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:

我有一个连接到 MySql 后端的 Microsoft Access 数据库。每当我运行此代码时，都会出现以下错误：

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.

3197 错误：Microsoft Office Access 数据库引擎停止了该进程，因为您和另一个用户正试图同时更改相同的数据。

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.

我认为 Access 数据库可能已损坏，因此我提取了较早的备份，但它正在做同样的事情，这让我认为这是 MySql 问题。

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

我们在链接到不同 MySql 表的该数据库的另一个版本上使用相同的一段代码，它工作正常。

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.

只是要补充一点，在第一个循环中， rs!MyFieldInTable 被更新，然后我得到了错误。

回答by Tim Lentine

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

您似乎没有移动到记录集中的另一条记录。简单地递增i不会移动到下一条记录。更传统的方法是在不需要其他变量（i和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

EDITAfter a bit of searching I came across this threadwhich 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!

编辑经过一番搜索，我遇到了这个线程，它似乎与您的问题相似。在该线程的底部，建议通过选择“高级”选项卡并选择“返回匹配行”选项来修改 MySQL DSN 的 ODBC 设置。该帖子还说要删除链接表，然后将其重新链接到您的 Access 数据库。我过去没有使用 MySQL 的 Access，所以我不知道这是否有效，所以请谨慎操作！

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

您还可以尝试更改您的记录集以将 dbOptimistic 标志用于记录集锁定选项，以查看是否有帮助：

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

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

回答by Dustin

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.

我遇到了同样的问题，结果证明我的解决方案是 BIT(1) 字段的默认值。Access 不喜欢这些为空。确保在 mysql 中为这些字段使用 0 或 1。

回答by mwolfe02

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

你可以尝试两件事。首先，尝试在打开记录集时添加 dbSeeChanges 选项：

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.

正如@HansUp 建议的那样，另一个选项是使用 SQL 更新语句而不是动态记录集。关键是将记录集作为快照打开，以便您对记录所做的更改不会影响记录集本身。

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

回答by XIVSolutions

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.

我这里没有 MySQL 来对此进行尝试，但在我看来，在执行 rs.Update 方法后，您的代码似乎没有推进记录集，因此您正在尝试在第一个记录中更新相同的字段。

Add this line after the rs.Update:

在 rs.Update 之后添加这一行：

rs.MoveNext

Hope that helps.

希望有帮助。

回答by HansUp

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

尝试从设置为 CurrentDb() 的对象变量调用 OpenRecordset，而不是直接从 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.

提出这个建议的原因是我发现直接在 CurrentDb 上操作会引发关于“块未设置”的错误。但是在使用对象变量时我没有收到错误消息。ISTR OpenRecordset 就是这样一个存在问题的操作。

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.

但是，我怀疑该示例是对记录集方法有用的现实世界情况的代理。这仍然让我想知道您在执行 UPDATE 语句时是否会看到相同或不同的错误。

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

如果您仍然遇到此问题，向我们展示 qryMyQuery 的 SQL 视图可能会有所帮助。

回答by Lumis

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.

我发现如果尝试保存与 MySql 记录中已有的数据相同的数据，Access 将显示这种错误。我已经尝试了该线程中的一些建议，但没有帮助。

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...

对此的简单解决方案是使用手动时间戳保存稍微不同的数据。下面是一个提升排序顺序字段并将其设置为 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.

我已经尝试在 MySql 表中使用自动时间戳，但是当新条目数据与旧条目数据相同时没有帮助。

回答by Amy Patterson

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.

我的小提示是，当将 SQL 表链接到 Microsoft Access 时，bits 是非常、非常、非常糟糕的数据类型，因为只有 SQL Server 了解 bit 是什么，Microsoft Access 很难解释 bit 是什么。将任何位数据类型更改为 int（整数）并重新链接您的表，以清除问题。此外，请确保您的 VBA 代码中的布尔值始终包含 1 或 0（不是 yes/no 或 true/flase），否则您的更新将无法更新到链接的 SQL 表，因为 Microsoft Access 将尝试使用 True 更新它们/False 或 Yes/No 和 SQL 不会喜欢那样。

回答by AntonioFico

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

我也有同样的问题；我解决了他们使用 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.

对不起，我的英语不好……我读了很多，但我从来没有学过！我只是意大利人。