Chaos When Copying Table Between Databases Using Transfer Database

Question

I have a need to let users create new tables that will contain given rows that are input to report processing. The user enters a name for identifying the instance of the report and the name of the table that is to contain the rows for the report. The report is part of an existing Microsoft Access suite of databases. The Itineraries table is input to the report process prior to the enhancement described herein.

My initial try failed.

The DoCmd method is being run in a front-end database with the notion of creating a new table structure in the back-end that would then be populated appropriately. The Itineraries table is located in the back-end database and is linked in the front-end. The result of the command was unexpected.

DoCmd.TransferDatabase acExport, _
“Microsoft Access”, “Path to backend database”, _
acTable, “Itineraries”, “TestTable”, True

I expected to have a table named TestBills in the backend database that I could link to from the front end database. This did not happen. The entire Itineraries table was copied into the backend database, sort of under the name TestBills. I say, “sort of”, because the TestBills table was a link in the backend database to the backend database itself! I could not link to this table from the frontend database.

Perry Sugerman Perry Sugerman · Accepted Answer · 2019-02-18T01:18:24

I decided to get around this by using the following code snippet:

DoCmd.TransferDatabase acImport, "Microsoft Access", dbBN, _
acTable, "Itineraries", "ExtraBills", True
DoCmd.TransferDatabase acExport, "Microsoft Access", dbTest, _
acTable, "ExtraBills", "TestBills", True
DoCmd.TransferDatabase acLink, "Microsoft Access", dbTest, _ 
acTable, "TestBills", "TestBills", True
CurrentDb.TableDefs.Delete ("ExtraBills")

“dbTest” and “dbBN” are constants with the paths to two backend databases. This snippet copies the Itineraries table structure from a back-end database into the frontend database renaming it “ExtraBills”. “Extrabills” is then exported to the Test database and named TestBills. The frontend then is linked to the TestBills Table in the backend and “ExtraBills” is deleted from the FrontEnd database.

This process achieves the desired result. I guess the moral is, “Do not Export table links using the TransferDatabase method of DoCmd.

Chaos When Copying Table Between Databases Using Transfer Database

1 Answers