CODEWITHSUNDEEP
vbams-accessms-access-2016linked-tables
Nathan_Sav
Nathan_Sav

Reputation: 8531

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

Upvotes: 1

Views: 1631

Answers (2)

Vlado
Vlado

Reputation: 888

I am using this code in Access 2016 and it works just fine:

Public Function RelinkTables(environment As Integer)

    On Error Resume Next
    Dim tblDef As DAO.TableDef

    For Each tblDef In CurrentDb.TableDefs    
        If tblDef.Connect <> "" Then
            tblDef.Connect = GetConnectionString(environment)
            tblDef.RefreshLink
        End If    
    Next

End Function

Public Function GetConnectionString(environment As Integer) As String
    Select Case environment
        Case 1 ' connection to Test db
            GetConnectionString = "your connection string to Test"
        Case 2  ' connection to Prod db
            GetConnectionString = "your connection string to Production"
    End Select
End Function

If this would not work with your db than may be the path is wrong.

Upvotes: 0

Erik A
Erik A

Reputation: 32682

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.

Upvotes: 1

Related Questions