How Do I Query A Recordset Object In VBA?

Hi all,

I've been banging my head against this problem for a while now and making no progress and am hoping someone cleverer than me can provide some assistance in solving it, or point me in the right direction.

Here goes....

I am working solely in the VBA environment and would like to perform an automated process that returns a recordset based on the results of a previously created recordset (assuming of course it is possible in the first instance.)

The catch is I would like to perform an INNER JOIN using the results held within a recordset.
I have already achieved this by creating a temporary table (using a SELECT INTO statement) and then removing the temporary table when I am finished but find this is a slower process than I would like.


I have three tables:
Table A - Fields: GROUP, NAME, DATA1, DATA2
Table B - Fields: GROUP, NAME, , CITY, DATA3
Table C - Fields: CITY, DATA3


I have the following declarations:

Dim db As Database
Dim rs1 As Recordset, rs2 As Recordset, rs3 As Recordset
Dim strSql As String

Set db = CurrentDb

Next, I select a specific range I am interested in. For the purpose of this example, I am only selecting GROUP values of 1,2 and 3.

strSql = "SELECT * FROM A WHERE A.GROUP IN (1,2,3)"
Set rs1 = db.OpenRecordset(strSql)
rs1.MoveFirst

Now, for each record returned in the query above, I would like to process as follows:

' // Build SQL statement
strSql = ""
strSql = strSql & "SELECT B.* "
strSql = strSql & "AS jc "
strSql = strSql & "FROM A "
strSql = strSql & "INNER JOIN B ON (A.Id = B.Id) "
strSql = strSql & "WHERE B.Name = " & rs1.Fields("Name")
strSql = strSql & " And A.Name = B.Name"

Set rs2 = db.OpenRecordset(strSql)
rs2.MoveFirst

The rs2 query returns the results of that query into what I though was a memory resident table referred to as "jc". (This is most likely where I am going wrong!)
Using this reference, I perform another query...

' // Build SQL statement
strSql = ""
strSql = strSql & "SELECT * FROM jc "
strSql = strSql & "INNER JOIN [C] ON (jc.City = C.City) "
strSql = strSql & "WHERE (([jc]![City] In ([jc]![City]) ))"

Set rs3 = db.OpenRecordset(strSql)
rs3.MoveFirst

And from this point I would be able to process the results (if any) that I need to....

...if it wasn't for the error:
"The Microsoft Jet database engine cannot find the input table or query 'jc'. Make sure it exists and that its name is spelled correctly."

I know the table doesn't 'truly' exist as a physical object or entity within the database, so how do I reference this 'phantom' table in a query?

I am happy to provide more details if it would help solve this problem and would really appreciate any feedback that anyone has to offer.

Thanks,

JC