Append recordset to table

G

George

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
 
K

Klatuu

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)
 
G

George

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
 
R

Rick Brandt

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.
 
K

Klatuu

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?
 
G

George Nicholson

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?
 
G

George

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
 
Top