CODEWITHSUNDEEP
sql-serverms-accessodbc
aitchpat
aitchpat

Reputation: 133

Migrating Access backend to SQL Server without breaking pre-existing linked tables

We have an Access db that acts as a company-wide backend--containing a master list of contacts, clients, etc. Almost every other database relies on this one via linked tables. However, we would like to migrate this backend to SQL Server with as few interruptions/breakages as possible. Our thought was to replace the tables in the Access backend with linked ODBC tables (keeping the same name), however when trying this we found that other Access files could no longer see the tables. Is there a way to do this or will we have to change all linked tables throughout our company to point to the new SQL backend?

Upvotes: 1

Views: 64

Answers (1)

HansUp
HansUp

Reputation: 97131

will we have to change all linked tables throughout our company to point to the new SQL backend?

Yes. You can create a link to a TableDef which is a native table in another Access database --- that is what you have now. But Access will not allow you to create a link to a TableDef in another Access database when that TableDef is actually a link to an object in yet another database. In other words, you can't link to a link.

So you must change all the links which point to the migrated Access tables so that they will now point directly to the SQL Server tables.

Upvotes: 1

Related Questions