VBA通过迭代ADODB结果集删除记录

发布于 2024-11-30 12:15:23 字数 352 浏览 2 评论 0原文

我正在尝试循环遍历 ADODB 结果集并在条件为真时删除记录。但是，当我执行此操作时，仅删除记录的第一个字段，其余记录仍保留。

有什么想法吗？我有以下代码：

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

原文

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.

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

猫卆 2024-12-07 12:15:23

我没有看到的一件事是 ytdRS.Open 命令。这可能是问题的（一部分）吗？

编辑：其他一些事情：

您在整个块（ytdRS）中没有使用相同的记录集名称，所以我不确定您的意图是否是使用两个不同的记录集（我假设不是）
我不是确定“Value”是否是记录集中字段的值（即ytdRS!FieldName.Value）还是变量名称。在这种情况下，它是一个变量名。
无论哪种方式，您几乎可以保证通过在 if 语句中使用 MoveNext 会遇到无限循环，因为除非 Value ，否则您的 Recordset 不会移动到下一条记录 等于 1。
我必须更改 CursorType 和 LockType 才能让您的示例在测试表上运行。我不认为 adOpenStatic 会允许您删除记录（我相信它会给您一个静态的或不可更改的游标）。当您在更新数据时遇到问题时，adOpenKeyset 通常似乎是解决问题的方法。用于锁定的 adLockBatchOptimistic 假定您正在批处理模式下操作；通常 adLockOptimistic 工作正常。有关详细信息，请参阅此处如果需要，可以删除方法和批量操作。

下面的代码对我有用；您必须针对您的特定应用程序对其进行编辑。特别是，您需要编辑 ytdRS.Source、Open() 方法中的 ActiveConnection:= 以及 ytdRs! [Order ID].Value = 36 行，与您发布的代码块中的“Value”语句相对应。

希望这有帮助！

如果您有任何疑问，请告诉我。

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

One thing I don't see is the ytdRS.Open command. Could this be (part of) the issue?

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 if statement, because your Recordset won't move to the next record unless the Value is 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 adOpenKeyset usually 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 here for more info on the Delete method and batch operation, if you need it.

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 = 36 line, to correspond to your "Value" statement in the block of code you posted.

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

回复收藏 0 原文

~没有更多了~