• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

Filter One Recordset by Another Recordset

I have two ADO recordsets in Access. They have two common fields (say, ID_1 and ID_2). I want the first recordset to be filtered so that it only includes items also in the second recordset. This is not a simple filter (say, ID_1 > 3 and ID_2 > 200), but more involved. I cannot change the original SQL query string that resulted in either recordset. Now, the simple way is to loop through the first and then inner loop through the second, but that is awfully slow for large recordsets. Is there a better way?
1 Solution
Basically, you can just order those 2 recordsets by id_1 and id_2, using "sort" property, then you will not need an inner loop to find out all the records exist in both recordsets. That is, if the recordcount of recordset1 is m, the recordcount if recordset2 is n, the loop will only be executed m+n times, not m*n .

For higher performance, you can use "Recordset.GetRows" method to put the recordset into an array, and then loop through the arrays instead of recordsets.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now