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

Question

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.

Answer 1

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

Answer 2

No strategy for linking tables changed the results, we would still get the same crash in the same database because of the same table.

However, disabling source code control fixed the issue, and nobody has suggested a possible reason for that, nor a method of investigating, so I'm closing the question by accepting "Disable SCC" as an answer.

Answer 3

I use MySQL linked tables a lot in my Access databases though any writing I do an ADO connection not the ODBC.

However recently in a new project I linked to a new database - a web backend MySQL - linking was fine - test connection was fine - but 1 particular table linked fine but try and open it - MS Access was instantly obliterated - something I have never seen in any of my databases using ODBC linking. The beauty of ODBC linking is it uses DAO not ADO and you can treat the table as a local table - not to be even ADO on this particular table errored - but did not give an error code to help.

Solved the problem - this table had 2 fields in type JSON (which is really only Long Text) but Access was killed - even with the latest driver 8.0

Luckily am in contact with web developer and this was a bespoke database but the JSON fields were not being used - so he converted them to Long Text - voila - MS Access was perfectly happy again.

Though the annoying thing I haven't solved is linked tables show contents as #DELETED# - and a F5 refresh is necessary to populate - though with driver 8.0 this is not working.

However, ADO is happy and a copy and paste link as a local table works perfectly.