Do you MoveNext after Delete

  • Thread starter Thread starter Robert
  • Start date Start date
R

Robert

I have 2 recordsets, rt and ru. I want to delete every record from rt where
there is a record in ru with the same value for "itemid":

Set rt = db.OpenRecordset(SQLstr1)
Set ru = db.OpenRecordset(SQLstr2)

If Not (rt.BOF And rt.EOF) Then
rt.MoveFirst
Do Until rt.EOF
If (ru.BOF And ru.EOF) Then
ru.MoveFirst
Do Until ru.EOF
If rt.Fields("itemid") = ru.Fields("itemid") Then
rt.Delete
End If
ru.MoveNext
Loop
End If
rt.MoveNext
Loop
End If

As you can see, if the itemid fields in rt and ru are equal then I do a
delete on rt. My question is, should there be a MoveNext after that? Since
a record has been deleted from rt that MoveNext won't cause it to skip a
record will it?

Robert
 
I have 2 recordsets, rt and ru. I want to delete every record from rt where
there is a record in ru with the same value for "itemid":
Click to expand...

How about a solution which will run much faster, with no VBA code at all?

DELETE rt.*
FROM rt INNER JOIN ru
ON rt.itemID = ru.itemID;

using the names of your tables instead of the names of the recordsets of
course.

If the recordsets are based on queries, use the queries, or just include the
criteria in this query.

John W. Vinson [MVP]
 
Thanks, I'll try it.
John W. Vinson said:
How about a solution which will run much faster, with no VBA code at all?

DELETE rt.*
FROM rt INNER JOIN ru
ON rt.itemID = ru.itemID;

using the names of your tables instead of the names of the recordsets of
course.

If the recordsets are based on queries, use the queries, or just include
the
criteria in this query.

John W. Vinson [MVP]
Click to expand...
 
Back
Top