Re: compare 2 recordsets

This seems to be working and is a lot faster:

230 rsNode.MoveFirst
240 Do While rsNode.EOF = False
250 Do While rsParentNode.Fields(lParentField) < rsNode.Fields(lNodeField) And _
rsParentNode.EOF = False
260 rsParentNode.MoveNext
270 If rsParentNode.Fields(lParentField) > rsNode.Fields(lNodeField) Then
280 rsNode.Delete adAffectCurrent
290 rsParentNode.MovePrevious
300 Exit Do
310 End If
320 Loop
330 rsNode.MoveNext
340 Loop
'this is important, otherwise rs.EOF will be true and nil will be moved to SQLite
350 rsNode.MoveFirst

I never worked much with recordsets and I realise now I needed some different settings to
do this and also need to disconnect the recordset.


RBS


"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> wrote in message news:eaTFtPjAIHA.4584@xxxxxxxxxxxxxxxxxxxxxxx
RB Smissaert wrote:
What would be the best (fastest) way to do this:
I have 2 ADO recordsets say A and B.
They both have a common field, say ID
Now I need to delete all the records in recordset A where
the value in the field ID is not found in the field in recordset B.
These are values of the datatype Long.
The ID field will be sorted ascending in both recordsets.

Now, I could do this with a double loop, but that could be a bit slow
and I wonder if this could be done better with a filter or find.

Why are you using recordsets for this? Are the recordsets from different databases?
If not, this is a job for SQL.
If so, there's no alternative to looping through one of them, but you can use Filter to get the corresponding records in the other recordset.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.

Relevant Pages

  • Re: dealing with max number of fields
    ... there is one big technical hurdle to overcome - I have no idea what you ... What is a JET table? ... You could, for example, open multiple recordsets to retrieve the data ... from the JET tables, loop through them, and print the data to a text file. ...
    (microsoft.public.access.externaldata)
  • Re: How to summarize recordset...Select Distinct alternative?
    ... Shaped recordsets may be the answer, ... You can loop through this one to using Filter to easily do the first ... Select FldItem,CountAs ItemCount ...
    (microsoft.public.data.ado)
  • Re: When do I open my Recordsets?
    ... >the first time. ... >Briefly, I loop through one recordset, and depending on certain field ... modify/add records in other tables (i.e. recordsets). ... designed Update queries to do the work instead of looping ...
    (microsoft.public.access.formscoding)
  • For Next Syntax
    ... replacing SQL statements with 2 new dynasets though to ... >Your syntax for the For Next loop is correct, ... > ActiveItemCode = rst!ItemCode ... >additional 'dynaset' recordsets and use those to perform ...
    (microsoft.public.access.modulesdaovba)
  • Re: compare 2 recordsets
    ... This made me think that maybe a double loop could be faster as you could set the start ... I think the FindNext method is the problem and maybe this is better ... And make sure you use disconnected client-side recordsets for this so you can free up the connections. ... This email account is my spam trap so I ...
    (microsoft.public.data.ado)