How do you temporarily hold output from a query

[smdesq]

Is there a way in ACCESS/97 to create a temporary table. I have a VBA module where I am using a SELECT INTO and want to put the output into a temporary table. Alternatively, I could do without the INTO part if there was a way to hold the output somewhere else, like in an array. I do the query with RunSQL. The output is 1 row of seven columns. thanks.

 

[tc3of4]

Yes you can, what you can do is create a temporary table then delete it.

Since you are working thru code, here is an example...

Code to create tempTable...

DoCmd.SetWarnings False
'/** Create the temporary table
DoCmd.RunSQL "SELECT aTable.* INTO tempTable FROM aTable"
DoCmd.SetWarnings True


Code to Delete Temporary Table
DoCmd.SetWarnings False
'/** Delete the temporary table
DoCmd.DeleteObject acTable, tempTable
DoCmd.SetWarnings True

This allows you to go into the table and do what you want to the data...

The other way is to create a recordset

example....

Private Sub aSubName()

dim dbs as database
dim rst as recordset
dim sqlString as string

set dbs = currentdb
sqlString = -some SQL select query string -
set rst = dbs.openrecordset(sqlString,dbopendynaset)

with rst
.movefirst
msgbox .[Field 1 Name]
msgbox .[Field 2 Name]
etc....
msgbox .[Field 7 Name]
end with

'/** This is where you can actually go to
'/** each field and retrieve each value.
'/** To get the value the code required is
'/** rst.[Field Name] <-- returns the value

set rst = nothing
set dbs = nothing

End Sub

What this does is create a temporary recordset and you can do whatever you want to it. No tables were created at all.

This also assumes that you always receive data, if no data then when you use the .movefirst code it will result in an error... you can modify it to catch errors later on....


Hope this helps,
tc3of4