DAO.Recordset.Update导致reckord锁定 [英] 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.

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

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

3197错误:Microsoft Office 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.

代码如下:

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

我认为Access数据库可能已损坏，因此我撤回了较早的备份，但是它所做的相同，这使我认为这是MySql问题.

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.

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

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.

只需在第一个循环中添加rs！MyFieldInTable，它就会更新，然后出现错误.

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

推荐答案

您似乎没有移到记录集中的另一条记录.简单地增加i不会移到下一条记录.一种更传统的方法是遍历记录集，而无需其他变量(i和rsCnt).

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

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

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!

您也可以尝试更改记录集以对记录集锁定选项使用dbOptimistic标志，以查看是否有帮助:

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)

这篇关于DAO.Recordset.Update导致reckord锁定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！