6

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.

Baxter
  • 5,633
  • 24
  • 69
  • 105

1 Answers1

7

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.

HansUp
  • 95,961
  • 11
  • 77
  • 135
  • "Track name autocorrect" is on by default as well as "perform name autocorrect". It still loses all of the relationships as well as all of the object dependencies? – Baxter Jan 22 '13 at 16:52
  • 1
    Track name autocorrect can't do anything for relationships with tables in external databases. Sorry if I didn't make that point clear enough. RE other object dependencies, see updated answer. – HansUp Jan 22 '13 at 18:40
  • Ah ok that makes perfect sense. Thank You. – Baxter Jan 22 '13 at 19:23
  • You're welcome. I realized mentioning track name autocorrect was not really useful for what your need here, so I removed it from the answer. – HansUp Jan 22 '13 at 19:30
  • 2
    Turning off the "track name autocorrect" was useful for renaming the old tables ex., "tblFoo" to "tblFoo_old" without having all the references to it, queries etc., also updating to reference "tblFoo_old". I can then bring in my ODBC-linked table as "tblFoo" and all the references remain accurate. – Baxter Jan 23 '13 at 14:31