Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    manipulating ADO recordsets dynamically (Access 2000)

    Is the following possible and if so how?

    Construct a ADO recordset containing one field (ID, numeric) and use this as a FILTER for another recordset. That means I want the second recordset to contain only those records with a matching record in the first recordset.

    I could modify the SQL directly but there are specific reasons that I don't want to do that (if I can avoid doing so) in this instance. I also would rather not (for performance) loop through the first recordset and construct the second recordset as a disconnected set.

    Thanks.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: manipulating ADO recordsets dynamically (Access 2000)

    Hi Andy,
    You could use the first recordset to construct an IN clause and then filter your second recordset using that - or is that what you were trying to avoid? You could also of course create temporary tables and join them on the ID fields.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: manipulating ADO recordsets dynamically (Access 2000)

    Thanks Rory.
    Yes, it's a (possibly/probably) silly idea to perform the filtering like an IN clause, just dynamically without modifying the SQL statement. This particular problem I have now sorted out with some changes to the SQL statements in the background.
    Temporary tables - I avoid them if I possibly can do because of the performance hit in creating / writing records ... In instances like that I sometimes use 'dummy' queries and modifiy the SQL on the fly (one report being used for different sources).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •