Re: compare 2 recordsets

Would Seek know that the rs is sorted so stop seeking if it is past the sought value?
This made me think that maybe a double loop could be faster as you could set the start
point and do exit do or exit loop when it has past the value.
What complicates matters is the delete. What happens to the position in the recordset after
a delete of the one single currrent record? Will it be in the next of the previous.
How would a filter work?
Still haven't worked it out yet.

RBS


"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> wrote in message news:%23W97UzqAIHA.5160@xxxxxxxxxxxxxxxxxxxxxxx
RB Smissaert wrote:
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.



I think you'll be better off using either Seek or Filter rather than FindNext. I would be surprised if a loop would outperform either of those methods.
And make sure you use disconnected client-side recordsets for this so you can free up the connections.

--
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: compare 2 recordsets
    ... I think the FindNext method is the problem and maybe this is better ... I would be surprised if a loop would outperform either of those ... And make sure you use disconnected client-side recordsets for this so you ... This email account is my spam trap so I ...
    (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)
  • For Next Syntax
    ... Your syntax for the For Next loop is correct, ... Set rst = CurrentDb.OpenRecordset(TempTable, _ ... ActiveItemCode = rst!ItemCode ... additional 'dynaset' recordsets and use those to perform ...
    (microsoft.public.access.modulesdaovba)