Append Query, multiple tables to multiple tables in another database

[jonyquek]

Can a Append Query move all my data stored in multiple tables to another database with a identical table structure?

Because as I try to work the query, I keep getting prompted to "Select a table" I want to append to, and I don't want to append to just one table...

 

[JohnLee]

Hi,

As far as I'm aware you can't append to more than one table at a time in an append query, hence why it prompts you to select which table to append to.

The way I have worked around this is to create a number of append queries that append to the various tables you want, then to create a module that runs a series of commands that runs each append query in what ever sequence you want, appending the required data to the required tables.

Someone else might know of a better way, as I'm no expert.

Hope this helps in someway

John

 

[jonyquek]

Thanks! I'll try that

 

[JohnLee]

Thanks! I'll try that

Hi again,

Here's an example of what I have done in one of my databases.

'Backup Process - Copy the tables listed below to the backup Database ADDLP BU.mdb
DoCmd.TransferDatabase acExport, "Microsoft Access", "Y:\ADDLP\Backup\ADDLP BU.mdb", acTable, "tblCompanyTypes", "tblCompanyTypes", False
DoCmd.TransferDatabase acExport, "Microsoft Access", "Y:\ADDLP\Backup\ADDLP BU.mdb", acTable, "tblDDMadateStats", "tblDDMadateStats", False
DoCmd.TransferDatabase acExport, "Microsoft Access", "Y:\ADDLP\Backup\ADDLP BU.mdb", acTable, "tblDDMandateImportHistory", "tblDDMandateImportHistory", False
DoCmd.TransferDatabase acExport, "Microsoft Access", "Y:\ADDLP\Backup\ADDLP BU.mdb", acTable, "tblDDMandateStatsDGI", "tblDDMandateStatsDGI", False
DoCmd.TransferDatabase acExport, "Microsoft Access", "Y:\ADDLP\Backup\ADDLP BU.mdb", acTable, "tblDDMandateStatsDGS", "tblDDMandateStatsDGS", False
DoCmd.TransferDatabase acExport, "Microsoft Access", "Y:\ADDLP\Backup\ADDLP BU.mdb", acTable, "tblDDMandateStatsSky", "tblDDMandateStatsSky", False
DoCmd.TransferDatabase acExport, "Microsoft Access", "Y:\ADDLP\Backup\ADDLP BU.mdb", acTable, "tblLetterTypeCode", "tblLetterTypeCode", False
DoCmd.TransferDatabase acExport, "Microsoft Access", "Y:\ADDLP\Backup\ADDLP BU.mdb", acTable, "tblREJ6StatsDGI", "tblREJ6StatsDGI", False
DoCmd.TransferDatabase acExport, "Microsoft Access", "Y:\ADDLP\Backup\ADDLP BU.mdb", acTable, "tblREJ6StatsDGS", "tblREJ6StatsDGS", False
DoCmd.TransferDatabase acExport, "Microsoft Access", "Y:\ADDLP\Backup\ADDLP BU.mdb", acTable, "tblREJ6StatsSky", "tblREJ6StatsSky", False
DoCmd.TransferDatabase acExport, "Microsoft Access", "Y:\ADDLP\Backup\ADDLP BU.mdb", acTable, "tblNoAddressStats", "tblNoAddressStats", False
DoCmd.TransferDatabase acExport, "Microsoft Access", "Y:\ADDLP\Backup\ADDLP BU.mdb", acTable, "tblNoNoPostCodeStats", "tblNoPostCodeStats", False
DoCmd.TransferDatabase acExport, "Microsoft Access", "Y:\ADDLP\Backup\ADDLP BU.mdb", acTable, "tblUserID", "tblUserID", False

Hope this helps.

John

 

[jonyquek]

Bring Up My Thread?