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