TiKiMoN

I must be having a brain freeze or something... I can't seem to work this out.

The problem I am having is that I have a database I use as the Connection for an ADODB Recordset to perform a query. The database (MS Access database) is read-only when I open it. (I do not want to modify it at all.) Now, I need to perform several queries on the tables in this database to arrive at the final result. My question is, how can I do this without storing temporary tables in the database? I would like to use multiple Recordset variables to achieve the desired query and then dispose of them at the end of the Sub, but I can't think whether or not it's possible to do that.

One alternative I had was to create a temporary database that I COULD write to, and then use loops to dump Recordsets into tables in the new database, until the final table was acqured. This seems a little inefficient though, so I was hoping someone else had a better suggestion. Thanks in advance!

diver

Yes, that is possible.

diver

TiKiMoN

No doubt a veritable fountain of wit.

However, what I was indirectly implying was that I needed to know more "how to do it" rather than "can it be done".

Say I have database "A". I use an ADODB Recordset ("B") to query 2 tables from A to form a resultant set. Now, I want to use another table from A and the results from B and use another Recordset variable ("C") to hold that result. And finally, one more table from A and another Recordset variable ("D") to hold the results of a query betwen A and C.

So... A -> B.
A | B -> C
A | C -> D

D is the final result I want. Now, without temporarily storing the query results into a separate database, how can I accomplish this using only Recordset variables? I tried using just one SQL statement, but it claims there is an "ambiguous join" and cannot complete the query. The final result needs to be stored in a Data control to populate a MSFlexGrid.

Thanks again for all of your help!!

diver

okay... I see what you are saying.
actually, I've never heard of anyone describing this logic to formulate recordsets. Granted, it can most likely be done, but with a lot of work. Logically, you can accomplish this in a much easier way.

For example, this is really a pseudocode you have explained:

Database: "Northwind"
Recordset1: SELECT straight from the database -ok

Recordset2: SELECT straight from the database, and also include the results from Recordset1.

Recordset3: SELECT straight from the database and also include the results from Recordset2.

Now, in reality, create ONE select statement and return ONE recordset. There are numerous ways to do this. You might want to check out a good SQL Syntax text, especially the UNION key word. Two issues still exist:

1) A Datacontrol or Datagrid accepts one (1) recordset only.
2) A Flexgrid cannot be hooked up to a Datacontrol.
3) You can, however use the Datagrid.

Hope that helps.
diver

TiKiMoN

Oops... I meant to say Datagrid, but MSFlexGrid came out. That is what I am using, so sorry for the confusion on that.

I guess I'll try using a union, but this is going to be one incredibly long SQL statement, provided it works. I will let you know the outcome here. Thanks again!