Open a recordset against another recordset?

[HartJF]

I have opened a snapshot-type recordset against a lengthy query. I want to query against the recordset, but I can't get recordset.openrecordset to work for me.

Here's the code:

set rst1=currentdb.openrecordset("tbl",openforwardonly)
set rst2=currentdb.openrecordset("qry",opensnapshot)
do
set rst3=rst2.openrecordset("SELECT * FROM rst2 WHERE fld = """ & rst1!str & """", dbopensnapshot)
rst1.movenext
loop until rst1.eof

I also tried replacing rst2 in ...FROM rst2... with qry to no avail. (I didn't expect to find qry in rst2)

I'm stumped. I suppose I could dump rst2 into a temporary table, but I hate littering the database (and, oh!, the bloat)

 

[Galaxiom]

Recordsets don't support what you are trying to do in the sql.

Temporary tables should be held in another separate local database. Many developers do put them inside the front end but it is very a bad practice.

 

[HartJF]

Thanks for the quick response, gal. I was afraid that I'd have to use a temporary table. This is a data-mining application, so I have the large table (1.6GB) separated from the queries, reports and code, but I hadn't thought about another database for the temporary tables. That's a great thought!

By the way, I'm a huge footy fan. My local public television channel replays a select game delayed by a week. I resisted the temptation to check afl.com.au for the result of the Grand Final Replay. I can't wait 'til March! Go Swans!