vbams-accessms-access-2016linked-tables

Changing Linked Table Source Access 2016

I am trying to change the links in an Access 2016 database, but the method I've used in the past is not working as required.

I am using the 

 t.connect="new connection"
 t.refreshlink

method, where t is a the table.

I have seen in the linked table manager that the tables are now grouped by a data source. I can create the new source and link it to the desired table, but I have many as migrating, so would like to do this in code.

I get no errors the current way, but immediately after the .refreshlink the table's .connect is still the same.

Is this still possible?

I currently populate a dictionary with the table name and it's existing connection, but only if non ODBC.

I am then looping through this dictionary, getting the table and changing its connection

CurrentDb.TableDefs(strTableName).Connect = strNewConnection
CurrentDb.TableDefs(strTableName).RefreshLink
Debug.Print CurrentDb.TableDefs(strTableName).Connect

Existing connection = ;DATABASE=\\app01\Access\CRM_Data.mdb New connection =;DATABASE=C:\CRM_TEST\CRM_DATA_BE_2016.accdb

Many thanks

Solution

  • You should not use CurrentDb.TableDefs when changing tables, as that changes between calls and makes the reference to the tabledef where you change the connection string be a different one than the one where you refresh the link.

    Dim d As DAO.Database
Set d = CurrentDb
d.TableDefs(strTableName).Connect = strNewConnection
d.TableDefs(strTableName).RefreshLink

    AFAIK this behaviour is not version-dependent, so the code you provided should never have worked.