Sql – ms access crashes when loading an mdb containing a linked sql server table

crashlinktablems-accessodbcsql-server

I have an mdb which used to contain a bunch of linked tables. These links point to tables in another Access mdb.

As part of a controlled migration, I'm changing these link table to point to an SQL server instance instead, by iterating through all linked tables and updating the connect string to an ODBC one, then calling RefreshLink on the tabledef.

However, on opening my new database with ODBC links, Access crashes. More interestingly, if I remove a single specific linked table (via ADO) I can then open up the database. Even more interestingly, if I add that linked table back in through the Access GUI, it doesn't crash, so I know it's not a problem with the table itself in SQL Server.

So, I need to figure out what it is about this particular linked table that causes Access to crash. Can I get at any kind of information about the crash to help? Where can I even start investigating this?

EDIT: I have tried a number of ways of refreshing the link table, either by Refresh Link, or dropping and recreating the tables with DSN or without DSN, etc. Every time it is the same table that causes the mdb to crash on opening.

EDIT 2: Sadly it seems that the crash is actually in some way down to source control – if I disable my SCCAPI provider then there's no crash. I still have no idea how to investigate this.

Best Solution

Delete the links and create entirely new ones. ODBC links cannot be reliably refreshed even when they start out as ODBC links.