Access replacing local table with ODBC linked table preserving original name references

Question

I am trying to replace all the local tables in my Access DB with linked tables from an ODBC data source. I am able to import the new table which comes in as "xyz_table". I want to replace the old local "table" with "xyz_table". However when I delete "table" or rename "xyz_table" to replace "table" it deletes all of the relationships / object dependencies of the original local "table". (queries, forms, reports etc.,)

Is there anyway to save/apply the relationships / object dependencies from the original local "table" to the new ODBC linked "xyz_table". I don't want to go through by hand and try to re-link all the relationships / object dependencies.

Any help would be greatly appreciated.

Answer 1

Access can't enforce referential integrity for linked tables, and won't allow you to create such relationhips. Create the relationships in the database which is the source of the linked tables.

To deal with object dependencies when changing from local to remote tables, rename the old tables to something else, and give the linked tables the original table names.

For example if I have a query based on a native Access table named "tblFoo". I would rename "tblFoo" to "tblFoo_old". Then name the replacement ODBC-linked table as "tblFoo". Ideally the query would still work correctly even though "tblFoo" was now a link instead of a local table.

The same technique works for tables referenced in forms and reports.

Beware of the Access option, track name autocorrect. It may update the dependent objects when you rename the original tables. Turn that option off so it won't interfere.