Re: compare 2 recordsets

I now have something that does work (and that is a start), but performance is not very good:

rsNode.MoveFirst
Do While rsNode.EOF = False
strCriterium = strParentField & " = " & rsNode.Fields(lNodeField)
If rsParentNode.FindNext(strCriterium) = False Then
rsNode.Delete adAffectCurrent
End If
rsNode.MoveNext
Loop

I think the FindNext method is the problem and maybe this is better with a double loop,
knowing that both recordsets are sorted ascending of the ID field.


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: Catch 22 - cant write to recordset
    ... in that case (selecting data that's not indexed) the server will run a table ... scan followed by a filter. ... > result as with recordsets and same good results as with the recordsets. ...
    (microsoft.public.data.ado)
  • 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: compare 2 recordsets
    ... I never worked much with recordsets and I realise now I needed some different settings to ... Now, I could do this with a double loop, but that could be a bit slow ... 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. ... This email account is my spam trap so I ...
    (microsoft.public.data.ado)
  • Re: compare 2 recordsets
    ... They both have a common field, ... The ID field will be sorted ascending in both recordsets. ... use Filter to get the corresponding records in the other recordset. ... This email account is my spam trap so I ...
    (microsoft.public.data.ado)
  • Re: How to determine if an ADO Recordset column is required
    ... > required column. ... > pre-validation to compare the incoming data with the schema ... don't use recordsets for data maintenance in ASP. ... This email account is my spam trap so I ...
    (microsoft.public.inetserver.asp.db)