Import Query using TransferDatabase Failing

[jdam]

I have an automated update function built into my Access application.

When I run the function in Access 2003 with SP2 I get broken joins in the queries that were imported using TransferDatabase command.

If I manually import the queries from the update db then the queries work. Also works fine on Access 2000.

Since my userbase is mixed with 2000 to 2003, I need to fix this. Is there a switch or parameter I am missing here.

Here is the line of code for the import:

DoCmd.TransferDatabase acImport, "Microsoft Access", xDB, acQuery, "Update_Query_1", "AddExistingAfeMissingDistributions", False

 

[jdam]

Further checking and debugging I have uncovered the following:

1. The query joins to an table that is linked via code as needed. Therefore, when the query is imported it is broken until the table is linked. Then the query can be run followed by the table link being dropped.

2. The docmd.transferdatabase command in 2003, seems to be opening and saving the query during the import process. This opens the query in it's broken state (because the linked table doesn't exist) then saves the query broken (thus killing joins etc to the linked table). Once the code gets to the point where it has relinked the table, and needs to run the query, the query dies because the links(joins) are gone from the query.

Is there a way in Access 2003 sp2 to stop the opening and saving behaviour on an import of a query?

Joel

 

[jdam]

Office 2003 has an option called "Enable AutoJoin" set by default. This combined with the autochecker for spelling etc. Checks the queries as then are imported and tries to correct the broken join.

By disabling the AutoJoin functionality, the import and subsequent code works.