Make table from recordset

  • Thread starter Thread starter alex
  • Start date Start date
A

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
 
I can’t figure out how to make a simple table with one column of
RecordNumbers (make table – deleted/made every time code executes):
Click to expand...

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.
 
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..
Click to expand...

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
 
Back
Top