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.Curso开发者_StackOverflow中文版rType = adOpenStatic
ytdRS.LockType = adLockBatchOptimistic
rst.MoveFirst
Do Until rst.EOF
if (value = 1) then
rst.Delete
rst.MoveNext
end if
Loop
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 theValue
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
精彩评论