Export Select Query Results as Table to New Database

[mgilbert86]

Hello,



I'm trying to find out if it is possible within VBA in Access to export the results of a select type query in x database and place/import them as a new table in y database.

I do not want to copy the query design / the query its self but the results.

Currently I have found the following code and am led to believe this works but can't get the syntax right.

Set dbs = OpenDatabase("C:\*X_database_location*")


dbs.Execute "SELECT queryname.* INTO IN 'C:\*Y_database_location*'" FROM queryname, dbFailOnError

Can you please help?

Thanks,

Michael

[June7]

Should be. I have code that inserts records into existing tables of other db. This avoids the issue of having to constantly delete and recreate tables in other db. The tables are established and I just replace the records.

'exports data to ConstructionExtract Access file
Dim strExtract As StringstrExtract = gstrBasePath & "Program\Editing\ConstructionExtract.accdb"
'delete records from ConstructionExtract tables
CurrentDb.Execute "DELETE FROM SampleInfo IN '" & strExtract & "'"
'insert records into ConstructionExtract tables
CurrentDb.Execute "INSERT INTO SampleInfo IN '" & strExtract & "' SELECT * FROM ConstructionSampleInfo;"

It is not even necessary to declare and set database object variable.

However, if you really need to create a new table each time, try:

CurrentDb.Execute "SELECT queryname.* INTO IN 'C:\*Y_database_location*' FROM queryname", dbFailOnError

Note the relocation of second quote mark to end of sql statement.

I presume you substitute actual db path and name for 'C:\*Y_database_location*'.

[ranman256]

attach the table from the outside db, then append the records to this table.
(same as an export)