-
I want to create a recordset using one query, then do a subsequent query on the result to do some more manipulation. However, I can find no syntax for selecting from a recordset variable (what are its parent database and table names?). Sometimes I can use a workaround by having the first query be a SELECT...INTO, yielding a new table in a known database from which I can do the subsequent query. But when I want the first query to be a crosstab, it seems not to support SELECT...INTO. My current workaround is a routine I wrote to copy a recordset to a table but it is a *CLUNKY KLUDGE*! Surely there is a more elegant way. Any help?
TIA
Bob Alei
-
Create a temporary querydef and run your second query against the querydef.
-
I use the following (assume you've already opened a database object):
Code:
dim rstmp as recordset
dim rstmp1 as recordset
sSQL= "select * from table1 where field=" & varaible
set rstmp=database.OpenRecordset(sSQL, dbOpenSnapshot)
If rsTmp.EOF = False Then
Do While Not rsTmp.EOF
key1 = rsTmp.Fields(0)
' [...]
lastfield = rstmp.fields(x)
sSql2 = "select something from table2 where item='" & key1 & "'
Set rsTmp2 = database.OpenRecordset(sSql2, dbOpenSnapshot)
If rsTmp2.EOF = False Then
' do something like read the fields
endif
rsTmp2.Close
rsTmp.MoveNext
loop
rsTmp.Close
End If
(hit return typing stuff in, not a good idea)
[This message has been edited by JHausmann (edited 08-23-1999).]
-
If you are using DAO you could use the Filter method on the existing recordset to get a subset....
Bash
