Access that replaces the local table with a linked ODBC table that retains the original name references
I am trying to replace all local tables in my access DB with linked tables from an ODBC data source. I can import a new table that comes in as "xyz_table". I want to replace the old local "table" with "xyz_table". However, when I drop the "table" or rename "xyz_table" to replace "table", it removes all dependencies of the relationships / objects of the original local "table". (requests, forms, reports, etc.)
Either way, keep / apply dependency / object dependencies from the original local "table" to the new ODBC bound "xyz_table". I don't want to go through manually and am trying to bind all dependencies of relations / objects.
Any help would be greatly appreciated.
source to share
Access cannot provide referential integrity for related tables and will not allow such relationships to be created. Create relationships in the database that is the source of the related tables.
To deal with object dependencies when moving from local to remote tables, rename the old tables to something else and give the linked tables the names of the original tables.
For example, if I have a query based on my own access table named "tblFoo". I would rename "tblFoo" to "tblFoo_old". Then name the ODBC-bound table to replace "tblFoo". Ideally, the query will work correctly even if "tblFoo" is now a link instead of a local table.
The same method works for tables specified in forms and reports.
Beware of the access option, autoreplace track name. It can update dependent objects when the original tables are renamed. Disable this option so it doesn't interfere.
source to share