[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 506
  • Last Modified:

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

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

1 Solution
Don't forget:

TD.RefreshLink after TD.Connect

DelphinidaeAuthor Commented:
Thank you

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now