Make table from recordset

[alex]

Make table from recordset

Hello,

I’m attempting to make a table based of a union parameter query, all
in VBA…

I have this code which prints the results in the immediate window, but
I can’t figure out how to make a simple table with one column of
RecordNumbers (make table – deleted/made every time code executes):
--------------------------------------------------------------------------
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Dim strLName As String
strLName = InputBox("Last Name?")
Dim strFName As String
strFName = InputBox("First Name?")
Dim strDOB As String
strDOB = InputBox("DOB?")

strSQL = _
"SELECT tblTest.RecordNumber as RecordNumber" & _
" FROM tblTest " & _
" WHERE (tblTest.Last_Name) Like """ & strLName & """" & _
" AND (tblTest.First_Name) Like """ & strFName & """" & _
" AND (tblTest.Date_Of_Birth) Like """ & strDOB & """"
‘ the rest of union query here….

'Debug.Print strSQL
Set dbs = CurrentDb()

' Create recordset based on SQL
Set rst = dbs.OpenRecordset(strSQL)

Do While Not rst.EOF
'Debug.Print rst![RecordNumber]
rst.MoveNext
Loop
'make tbl here?
rst.Close
dbs.Close

 

[John W. Vinson]

I can’t figure out how to make a simple table with one column of
RecordNumbers (make table – deleted/made every time code executes):

That's going to be slow, inefficient, bloat your database, and provide very
little benefit.

What can you do with this table that you cannot do with a recordset based on
the query? Precious little!

If you DO need it, a better approach is to have a prebuilt table (with the
appropriate datatypes, field size and indexing); use an Append query to
migrate the data into it, and a Delete query to empty it when you're done.

 

[alex]

That's going to be slow, inefficient, bloat your database, and provide very
little benefit.

What can you do with this table that you cannot do with a recordset basedon
the query? Precious little!

If you DO need it, a better approach is to have a prebuilt table (with the
appropriate datatypes, field size and indexing); use an Append query to
migrate the data into it, and a Delete query to empty it when you're done..

Hi John, thanks for helping.
I don't understand what you mean by "a recordset based on the query?"

I need the recordset results above as a basis for a second query. (I
think) I can export the results into a table and use the results for
the second query, or bypass the table and use the recordset results
(in code) for the second query without using a table. Is that what
you're talking about? I thought a table would be helpful for
troubleshooting! There should only be 5 to 10 results per query...I
think I can compact/repair in code as well.

I don't understand how it will bloat the database when the recordset
is closed (unless you're talking about creating many tables without
compacting). As far as speed; it seems to run rather fast. I don't
know a lot about recordsets so any advice would be helpful.

Thanks for your help,
alex