Insert Pass-Through Results into Access Table with VB

[raynman1972]

I have a pass-through query in some VB script that works fine and returns the correct data. I have searched the web for some code that could shed some light on how to take these results and insert them into an existing Access table using VB, but no luck. How can I make this work?

Private Sub RunPassThrough()

Dim qdfPassThrough As DAO.QueryDef, MyDB As Database
Dim strConnect, strSQL As String

strSQL = "BLAH"

If Not IsNull(CurrentDb.QueryDefs("qrySQLPass").SQL) Then 'doesn't exist
CurrentDb.QueryDefs.Delete "qrySQLPass"
End If

Set MyDB = CurrentDb()

Set qdfPassThrough = MyDB.CreateQueryDef("qrySQLPass")

strConnect = "BLAH;"

qdfPassThrough.Connect = "ODBC;" & strConnect
qdfPassThrough.SQL = strSQL
qdfPassThrough.ReturnsRecords = True
qdfPassThrough.Close

Application.RefreshDatabaseWindow

DoCmd.OpenQuery "qrySQLPass", acViewNormal, acReadOnly
DoCmd.Maximize

[JoeM]

I would recommend building your pass through query like you have done. Then if you want to append those records to an existing table, use the pass-through query in an Append Query to write them to your table. Append queries are pretty easy to run in VBA. If you aren't sure of the syntax, a good way is to create a Macro to run (open) the Append Query, save the Macro, and then use the "Convert Macros to Visual Basic" to convert the macro to VBA code.

[raynman1972]

That was almost too easy! And I got to try my hand at the whole Macro's piece for the first time. Thanks a bunch.

[JoeM]

Your welcome. Glad it worked out for you.