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

ms-access replace odbc table-relationships linked-tables
Baxter picture Baxter · Jan 22, 2013 · Viewed 9.6k times · Source

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

HansUp picture HansUp · Jan 22, 2013

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.

Related questions

How to do INSERT into a table records extracted from another table

I'm trying to write a query that extracts and transforms data from a table and then insert those data into another table. Yes, this is a data warehousing query and I'm doing it in MS Access. So basically I want …

Read More
How to SUM two fields within an SQL query

I need to get the total of two fields which are within the same row and input that number in a field at the end of that same row. This is my code. Sum(tbl1.fld1 + tbl1.fld2) AS [Total] …

Read More
Code to loop through all records in MS Access

I need a code to loop through all the records in a table so I can extract some data. In addition to this, is it also possible to loop through filtered records and, again, extract data? Thanks!

Read More