manipulating ADO recordsets dynamically (Access 2000)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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).

Reply | Quote