1

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.

Paul Smith
  • 1,044
  • 2
  • 13
  • 29

3 Answers3

0

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

David-W-Fenton
  • 22,871
  • 4
  • 45
  • 58
  • 1
    I agree. Write code that at startup deletes and recreates all your links before doing anything else. It should be very fast. You can use a local table with either a list of tables and the link they need, or simply a single row with the server to connect to (with the assumption that all linked tables should be recreated and will connect to the same server, and the presence of the linked table is what triggers its deletion and re-creation). – ErikE Jan 16 '10 at 18:52
  • It just occurred to me that whether or not ODBC links can be updated successfully depends on the ODBC provider. For instance, links to the accounting program MYOB's ODBC driver can be updated just fine without having to delete and recreate them, but in my experience, links to SQL Server and MySQL cannot. – David-W-Fenton Jan 16 '10 at 22:58
  • @Emtucifor: Actually, you don't need to do anything other than store the table names in a custom collection or an array before deleting and recreating them -- no need for persistent storage. – David-W-Fenton Jan 16 '10 at 22:59
  • It is often useful to have a table of tables that should appear in the dataabse, so why not use it for linking? I do not see an advantage in cluttering up code with lists. – Fionnuala Jan 17 '10 at 10:48
  • This didn't work I'm afraid. The same database crashes on the same table. Pastie of the VBA here http://pastie.org/783213 to see if you think I'm doing anything retarded. – Paul Smith Jan 18 '10 at 15:20
  • @Remou: I wasn't suggesting putting the list of tables in the code. I was suggesting walking the TableDefs collection and storing the list of ODBC linked tables in a memory structure, then deleting them all and recreating them from the list in memory (a custom collection would do, an array would be more flexible). I've never seen an Access app with a table storing a list of tables -- seems to me to be a completely nonstandard Access app component. – David-W-Fenton Jan 18 '10 at 20:43
  • @Paul Smith: your connect string looks very strange to me. What's Office 2003 got to do with it? – David-W-Fenton Jan 18 '10 at 20:45
  • @D W Fenton, I have found that link tables can go missing for a few reasons, so it can be useful to know what should be there. – Fionnuala Jan 18 '10 at 21:09
  • @Remou: I've never had linked tables "go missing" at any point in my 14 years of professional Access development. I'd be interested in hearing about the scenario where that was the case since it's not one that I've encountered in my garden-variety Access programming. – David-W-Fenton Jan 19 '10 at 20:31
  • Have you already applied the Microsoft's latest updates to Access? Maybe it's a bug in Access itself that's getting triggered? – Darth Continent Jan 21 '10 at 14:45
0

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.

Paul Smith
  • 1,044
  • 2
  • 13
  • 29
0

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.

Howard George
  • 1