Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 997
Baxter Avatar asked Jan 22 '13 16:01

Baxter


1 Answers

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.

like image 186
HansUp Avatar answered Sep 24 '22 00:09

HansUp