Access リンクテーブル の Connection を直接書き換えるVBA

引用元: ODBCの矛先変更について − Database Expert − ＠IT.htm

Option Compare Database
Option Explicit

Public Sub EditODBCConnection()
    Dim connectString As String
    connectString = "DRIVER=SQL Server;SERVER=127.0.0.1;UID=xxx;PWD=xxx;APP=Microsoft Data Access Components;WSID=xxx;DATABASE=xxx"
    
    Dim tdf As TableDef
    Dim db As Database
    Set db = CurrentDb
    
    For Each tdf In db.TableDefs
        If 0 < (tdf.Attributes And dbAttachedODBC) Then
            Debug.Print "old: "; tdf.Connect
            tdf.Connect = connectString
            tdf.RefreshLink
            Debug.Print " new:"; tdf.Connect
        End If
        Set tdf = Nothing           '必要かも？
    Next
    Set db = Nothing           '追加コード
End Sub