Run Query to create a Table in second MDB

  • Thread starter Thread starter Dkline
  • Start date Start date
D

Dkline

I have been presented a problem by the SVP. He has 20 queries in a database.
He wants to have some code that will run each query and have it create a
table in another database. Any existing tables in the second database will
be replaced in entirety by the newly created table. I'm just going to blow
them away at the outset.

So the "source" is "DataQueries.mdb". The first query is named "qryDC". The
"target" database is "DataTables.mdb". The first query should produce a
table titled "tblDC".

I know the name of each query in the "source". I know the name of the table
the results of the query should create in the "target". Basic question is
how in VBA can I run the query and have it create a table in the "target"?

How can I do this?
 
Set your queries up as Select queries. Create a Module with the following
code:

Function ExportTables()

DoCmd.TransferDatabase acExport, "Microsoft Access", "Path to the source
db", acTable, "Name of Select Query", "Name you want table to be", False

End Function
 
In the make table query (or append, either way), click on
Query, Make Table.

There is an option to select the name of the table to
create, as well as a option box to make it in the current
db, or to an external. Add the complete path to the
external db, and save it.

SQL for it is:

SELECT Main.* INTO Main IN 'c:\test.mdb' FROM Main


There, you're done.



Chris Nebinger
 
Back
Top