CODEWITHSUNDEEP
vbaexceladoresultset
jerry
jerry

Reputation: 11

VBA Deleting records by iterating through ADODB Result Set

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

Upvotes: 0

Views: 12053

Answers (1)

transistor1
transistor1

Reputation: 2925

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

EDIT: A few other things:

  1. 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)
  2. 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.
  3. 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.
  4. 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

Upvotes: 0

Related Questions