How do I query a recordset object in VBA?

[jc1104]

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

 

[Banana]

Instead of creating a table in memory, create a empty, temporary table in your database. When running the code, have it add the records to this empty table, then you can create queries from that temporary table.

 

[jc1104]

Thanks for the reply Banana.

I have a version which creates a temporary table (as you suggested), then removes it when complete. The tables currently have very little information in them (less than 20 records) and it takes a couple of seconds to process as INSERT INTO queries.

I'm kind of hoping that by eliminating the INSERT INTO part, it will speed things up as I expect this table to run into thousands of records.

Any other suggestions are welcome.

 

[Banana]

No other suggestions here, though I would like to add that I believe that in general, any action queries, including INSERT INTO, are more effective and quicker than recordset.

Maybe someone else other has other suggestions, though.

Good luck.

 

[The_Doc_Man]

A pre-stored query into a pre-defined table can be analyzed by the Access query optimizer. It doesn't matter if the table happens to be empty at the time as long as its indexes/keys are defined. It also doesn't matter if you have parameters to the query if you intend to always use them.

Doing something with a recordset is faster than doing it with a query - if and only if you are a really good VBA programmer and understand the nature of the recordset you are mucking. If you don't know how to optimize queries, you are better off letting Access do it using tried and true methods. If you try to do something impossible, though, it is going to take a LOT longer than if you merely tried to do something that is merely difficult.

Now to the error... you are having a mental-image problem of your JC table. it doesn't exist. There is no such thing as a memory-resident table in Access because of binding issues. There is nothing to bind. Let me explain...

Your recordset manager runs SQL in the background to manage recordsets and passes back the records it finds. That background copy of SQL cannot "see" your VBA memory because that is possibly in a child task or possibly in a .DLL, in either case with different memory management setup than your VBA code. Certainly, anything you build on your own as memory-resident cannot exist to anything else that runs in a restricted memory area and is already compiled and linked. The interface to that object doesn't let you specify a memory resident table because there is no way to specify it.

SQL has only one way to find data and that is to get it from the mSysObjects table - as a table or query object. JC, being neither class of object, does not reside there. Further, there is no object-class code that describes the kind of thing you are building in memory so that you could add to mSysObjects (or whatever the name of the system table happens to be.) You can't describe it because the design of Access never envisioned it. Therefore, you are S.O.L. (the last two letters are "outta luck".)

Go through a tabular intermediate because recordsets have NO OTHER INTERFACE for it.

 

[jc1104]

Thanks for the length reply The_Doc_Man.

I figured the problem was to do with the 'phantom' non-existent JC table but couldn't work out why. Your explanation is spot on and I now fully understand why I can't get it to do what I was hoping to do.

Using a pre-stored query might assist, I'll have to investigate. That said, my original method of creating a temporary table does in fact work - maybe I'm just being too impatient.

Thanks again for spending the time to explain why.

JC