I have this query below:

INSERT INTO TEST_DOC
SELECT *
FROM MPI_ADDSS_IFF;

I currently have 10 tables in my database (All with the same colunm names and formats) however i want to click a button that will put all the tables into TEST_DOC, instead of doing it one by one. To do this this i would like some code either in SQL or VBA that will do this.

Any ideas or help?

Kind Regards
Hewstone999

The easiest way to do this is to create 10 Update queries (after you run an Delete query on the receiving table to remove old data.) That would look like this:

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDELETE_OldData"
DoCmd.OpenQuery "qryAPPEND_Table01"
DoCmd.OpenQuery "qryAPPEND_Table02"
DoCmd.OpenQuery "qryAPPEND_Table03"
DoCmd.OpenQuery "qryAPPEND_Table04"
DoCmd.OpenQuery "qryAPPEND_Table05"
DoCmd.OpenQuery "qryAPPEND_Table06"
DoCmd.OpenQuery "qryAPPEND_Table07"
DoCmd.OpenQuery "qryAPPEND_Table08"
DoCmd.OpenQuery "qryAPPEND_Table09"
DoCmd.OpenQuery "qryAPPEND_Table10"
DoCmd.SetWarnings True

Did that help?


mmcdonal

Look it up at: http://wrox.books24x7.com

Sorry, create 10 APPEND queries, not Update queries.

mmcdonal

Look it up at: http://wrox.books24x7.com