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

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.

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).

Emtucifor 2010-01-16 18:52:41

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 2010-01-16 22:58:48

@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 2010-01-16 22:59:51

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.

Remou 2010-01-17 10:48:39

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 2010-01-18 15:20:33

@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 2010-01-18 20:43:42

@Paul Smith: your connect string looks very strange to me. What's Office 2003 got to do with it?

David-W-Fenton 2010-01-18 20:45:05

@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.

Remou 2010-01-18 21:09:46

@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 2010-01-19 20:31:55

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 2010-01-21 14:45:28

ansaurus

tags:

views:

answers:

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

related questions