Append recordset to table

[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

 

[Guest]

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)

 

[Guest]

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

 

[Rick Brandt]

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?

Use the Pass-Through as the input to the append query.

 

[Guest]

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?

 

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

 

[Guest]

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