Modifying linked tables VIA VBA

Posted on 2007-07-31
Last Modified: 2013-11-27
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.

Question by:Delphinidae
    LVL 6

    Accepted Solution

    Don't forget:

    TD.RefreshLink after TD.Connect


    Author Comment

    Thank you

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now