VBA 通过迭代 ADODB 结果集来删除记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7099818/
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
VBA Deleting records by iterating through ADODB Result Set
提问by jerry
I am trying to loop through an ADODB resultset and delete the record if a condition is true. However, when I do this only the first field of the record is deleted the rest of the record remains.
我正在尝试遍历 ADODB 结果集并在条件为真时删除记录。但是，当我这样做时，只有记录的第一个字段被删除，其余的记录仍然存在。
Any Ideas? I have the following code:
有任何想法吗？我有以下代码：
Set ytdRS = New ADODB.Recordset
ytdRS.Source = SQL_YTD
ytdRS.CursorType = adOpenStatic
ytdRS.LockType = adLockBatchOptimistic
rst.MoveFirst
Do Until rst.EOF
if (value = 1) then
rst.Delete
rst.MoveNext
end if
Loop
回答by transistor1
One thing I don't see is the ytdRS.Open command. Could this be (part of) the issue?
我没有看到的一件事是 ytdRS.Open 命令。这可能是（部分）问题吗？
EDIT: A few other things:
编辑：其他一些事情：
- You're not using the same recordset name throughout this block (ytdRS), so I'm not sure if your intention is to use two different recordsets (I'm assuming it's not)
- I'm not sure if "Value" is intended to be the value of a field in the recordset (i.e.
ytdRS!FieldName.Value) or a variable name. In this context it is a variable name. - Either way you're almost guaranteeing that you're going to hit an endless loop by having your MoveNext within the
ifstatement, because your Recordset won't move to the next record unless theValueis equal to 1. - I had to change the CursorType and LockType to get your example to work on a test table. I do not think an adOpenStatic will allow you to delete records (I believe it gives you a static, or unchangeable, cursor). An adOpenKeysetusually seems to be the way to go when you run into problems updating data. The adLockBatchOptimistic that you used for locking assumes you are operating in batch mode; normally adLockOptimistic works fine. See herefor more info on the Delete method and batch operation, if you need it.
- 您没有在整个块 (ytdRS) 中使用相同的记录集名称，所以我不确定您是否打算使用两个不同的记录集（我假设不是）
- 我不确定“值”是否是记录集中字段的值（即
ytdRS!FieldName.Value）或变量名称。在这种情况下，它是一个变量名。 - 无论哪种方式，您几乎都可以通过在
if语句中包含MoveNext 来保证您将进入无限循环，因为除非Value等于 1，否则您的 Recordset 不会移动到下一条记录。 - 我必须更改 CursorType 和 LockType 才能让您的示例在测试表上工作。我认为 adOpenStatic 不会允许您删除记录（我相信它会给您一个静态的或不可更改的游标）。一个adOpenKeyset通常似乎是当你进入更新数据问题的路要走。您用于锁定的 adLockBatchOptimistic 假定您在批处理模式下运行；通常 adLockOptimistic 工作正常。如果需要，请参阅此处了解有关 Delete 方法和批处理操作的更多信息。
The code below worked for me; you will have to edit it for your specific application. In particular you will need to edit ytdRS.Source, the ActiveConnection:=in the Open()method, and the ytdRs![Order ID].Value = 36line, to correspond to your "Value" statement in the block of code you posted.
下面的代码对我有用；您必须针对您的特定应用程序对其进行编辑。特别是你需要编辑ytdRS.Source时，ActiveConnection:=在Open()方法和ytdRs![Order ID].Value = 36线路，对应于你的“价值”语句您发布的代码块。
Hope this helps!
希望这可以帮助！
Please let me know if you have any questions.
请让我知道，如果你有任何问题。
Sub testme()
Dim ytdRs As ADODB.Recordset
Set ytdRs = New ADODB.Recordset
ytdRs.Source = "SELECT * FROM [Order Summary 2]"
ytdRs.CursorType = adOpenKeyset
ytdRs.LockType = adLockOptimistic
ytdRs.Open ActiveConnection:=CurrentProject.Connection
ytdRs.MoveFirst
Do Until ytdRs.EOF
If (ytdRs![Order ID].Value = 36) Then
ytdRs.Delete
End If
ytdRs.MoveNext
Loop
End Sub
