Modifying linked tables VIA VBA

I have started code to modify linked tables and linked quries to use a System DSN instead of being coded to the server.  The change for the pass through queries worked, however I am not able to do the same with the tables.  The code to change the pass through queries is:

Public Sub sbResetPassThroughQueryConnections()
Dim qd As QueryDef
Dim intSubStringLoc As Integer
For Each qd In CurrentDb.QueryDefs 'loop though each pass through query
    If Len(qd.Connect) > 0 Then ' find ones with a connection string
    intSubStringLoc = InStr(qd.Connect, "Server\Coding_db") ' find ones linked to Coding_db
    If intSubStringLoc > 0 Then
            'change connection string to the systems ODBC if found
       qd.Connect = "ODBC;DSN=CODING;DATABASE=Coding;Trusted_Connection=Yes"
    End If

I try the same logic to modify the linked tables, but am not sure where I am going wrong:

Dim TD As TableDef
Dim intSubStringLoc As Integer
    For Each TD In CurrentDb.TableDefs
    If Len(TD.Connect) > 0 Then
   
    intSubStringLoc = InStr(TD.Connect, "Server\Coding_db")
    If intSubStringLoc > 0 Then

    TD.Connect = "ODBC;DSN=CODING;DATABASE=Coding;Trusted_Connection=Yes"
    End If
    End If
    Next

This never updates the links to the tables, only the links to the pass through queries.

       
DelphinidaeAsked:
Who is Participating?
 
mmconsultantConnect With a Mentor OwnerCommented:
Don't forget:

TD.RefreshLink after TD.Connect


0
 
DelphinidaeAuthor Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.