Filter One Recordset by Another Recordset

Posted on 2006-05-19
Last Modified: 2010-05-01
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?
Question by:GivenRandy
    1 Comment
    LVL 6

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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Introduction In a recent article ( for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
    Article by: Martin
    Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now