Access VBA 中的 ADO 批量更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1042924/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ADO Batch Update in Access VBA
提问by jwoolard
I am trying to use batch updates in Access VBA. My code works fine when I update after every operation, but fails when I try to operate on multiple rows. The exception I get is
我正在尝试在 Access VBA 中使用批量更新。当我在每次操作后更新时，我的代码工作正常，但当我尝试对多行进行操作时失败。我得到的例外是
"Run-time error '-2147217887 (80040e21)': Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."
“运行时错误‘-2147217887 (80040e21)’：多步 OLE DB 操作产生了错误。检查每个 OLE DB 状态值（如果可用）。未完成任何工作。”
My code is
我的代码是
rs.Open "dbrammDump", CurrentProject.Connection, _
adOpenKeyset, adLockBatchOptimistic
rowsDel = 0
Do Until rs.RecordCount < 1
rs.MoveFirst
rs.Delete
rowsDel = rowsDel + 1
Loop
rs.UpdateBatch
Any ideas what the issue is?
任何想法是什么问题？
采纳答案by onedaywhen
I think the issue is that you need to explicitly use a client-side cursor. I suspect that you are implicitly using a server-side cursor.
我认为问题在于您需要明确使用客户端游标。我怀疑您隐式地使用了服务器端游标。
I prefer to set the recordset object's properties individually because I think it is easier to read (and therefore debug) than using the overloaded Openmethod. Also, you can use the RecordCountproperty for your loop e.g.
我更喜欢单独设置记录集对象的属性，因为我认为它比使用重载Open方法更容易阅读（因此调试）。此外，您可以RecordCount为循环使用该属性，例如
With rs
.ActiveConnection = CurrentProject.Connection
.Source = "dbrammDump"
.CursorLocation = adUseClient ' <<< THIS MISSING FROM ORIGINAL CODE
.CursorType = adOpenKeyset
.LockType = adLockBatchOptimistic
.Open
Dim counter As Long
For counter = 0 To .RecordCount - 1
.Delete
.MoveNext
Next
.UpdateBatch
rowsDel = counter
End With
FWIW I agree with others here that a set-based solution in SQL is preferable to procedural code such as that above.
FWIW 我同意这里的其他人的观点，即 SQL 中基于集合的解决方案比上面的过程代码更可取。
回答by shahkalpesh
Why are you deleting records in this manner, when you could call myconnection.Execute "DELETE FROM myTable WHERE....."?
当您可以调用时，为什么要以这种方式删除记录myconnection.Execute "DELETE FROM myTable WHERE....."？
Also, how have you opened the recordset? Paste the myrecordset.Open(...)statement here.
See if this linkhelps.
另外，你是如何打开记录集的？将myrecordset.Open(...)声明粘贴到此处。
看看这个链接是否有帮助。
回答by Tony Toews
Is the source of your inserts a data set that can be used in a SELECT statement to be appended as a batch? If so, that's your answer.
您的插入源是否是一个数据集，可以在 SELECT 语句中使用以作为批处理附加？如果是这样，这就是你的答案。
If you need the number of rows deleted/inserted/updated with ADO code use the following.
如果您需要使用 ADO 代码删除/插入/更新的行数，请使用以下内容。
CurrentProject.Connection.Execute strSQL, lngRecordsAffected, adCmdText
