Append recordset to table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a VBA question. In my program, I create a recordset through SQL
(contained in strSQL) as follows:

Set rstSQL = myODBC.OpenRecordset(strSQL)

Now I would like to append the result of this recordset to a table in
Access. Is there an efficient way to do this? For example:
db.INSERT rstSQL into tblResult (obviously this won't work, but it's what I
want!)

Thanks,
George
 
You don't need the select query you are using. Make it into an append query
that pulls the records based on the criteria in your select query and appends
to your Access table.

So instead of:
Set rstSQL = myODBC.OpenRecordset(strSQL)
You would need
Currentdb.Execute(newSQL)
 
The query I have defined in strSQL is a pass-through query to a DB2 database.
Access cannot run it and an append query at the same time. Is there another
way?

Thanks
 
George said:
The query I have defined in strSQL is a pass-through query to a DB2
database. Access cannot run it and an append query at the same time.
Is there another way?
Click to expand...

Use the Pass-Through as the input to the append query.
 
Sorry, I should have known. But, I don't think there is that big a problem.
A query can be based on a query. Have you tried creating the append query
and use your pass-through as the source?
 
Assign your 1st SQL to a named querydef rather than a recordset. Then create
a 2nd SQL string that appends the querydef to your table?
 
Thanks for the idea - all three replies suggest the same approach: define an
append query that uses the pass-through query as the source. In VBA, how do
I do this with recordsets? I already have rstSQL.

Something like this?:
strSQL2 = "SELECT * FROM " & rstSQL & " INTO [TblResult]"
db.Execute(strSQL2)

Thanks
 
Back
Top