Update an Access Link Table when the underlying SQL Server table's columns change

Question

At work we've got a SQL Server database that several users connect to (read only) using Access 2003.

This was fine, except now one of the tables they look at is re-created fairly often and part of this process involves a cross-tab query.

Which means that, depending on the data, the number and and names of the columns potentially change each time the table is regenerated.

However when they look at the re-created table from Access, it still shows the column headings that were there when the table was first linked to.

Is there a way I can programmatically re-link the table each time they open the Access database?

Answer 1

Something like this snippet is usually used. Search google for 'ms access refresh link table' and you'll find various solutions all similar to this one.

Answer 2

ODBC linked tables break when the table or view on the server is altered. Some changes can result in them just becoming read-only, others will simply not include all the columns.

I have found that updating the connect string does not successfully fix this problem. It will usually fix missing fields, but it can still be read-only. The only reliable way to do this is to recreate the linked table on the fly.

Another alternative would be to not use a linked table at all, but use a saved QueryDef that has the appropriate connect string. This will never have to be updated, but could be a performance issue as the metadata stored in the table link helps Access figure out how to retrieve the data. Without that metadata stored in the table link, it has to retrieve that information from the server each time the query is run.

Answer 3

What I ended up doing was creating a VBA function that looks something like below (needs error handling!)

Public Function ReConnectToLinkTable()

Dim db As Dao.Database
Dim tdf As Dao.TableDef

Set db = CurrentDb
Set tdf = db.CreateTableDef("local_table_name")

tdf.Connect = "ODBC;DRIVER=SQL Server;SERVER=server_name;UID=user_name;" & _ 
              "PWD=password;APP=Microsoft Data Access Conponents;" & _
              "DATABASE=database_name"
tdf.Attributes = TableDefAttributeEnum.dbAttachSavePWD
tdf.SourceTableName = "server_table_name"    

db.TableDefs.Delete ("local_table_name")
db.TableDefs.Append tdf

End Function

Then I created a macro called AutoExec (the name guarantees it is called when the Access file is opened) which has an Action of RunCode, which calls the ReconnectToLinkTable() function.