Access VBA 程式碼自動重新連結 SQL Server 連結資料表

應用場景

Access 前端 (.mdb/.accdb) 連結 SQL Server 後端資料表，當伺服器遷移、帳號變更或 DSN 異動時，需批次更新連結屬性。

通用重連函數

Function RelinkSQLTables()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim connStr As String

    ' 連線字串組裝 (檔案 DSN + 認證 + TCP/IP)
    connStr = "FILEDSN=D:\Demo\Steel.dsn;UID=sa;PWD=abc;WSID=;DATABASE=MyDB;Network=DBMSSOCN"

    Set db = CurrentDb
    For Each tdf In db.TableDefs
        ' 536870912 = dbAttachedODBC (連結 ODBC 資料表)
        If (tdf.Attributes And dbAttachedODBC) = dbAttachedODBC Then
            tdf.Connect = connStr
            tdf.Attributes = dbAttachSavePWD  ' 儲存密碼避免重複提示
            tdf.RefreshLink
        End If
    Next
    Set tdf = Nothing
    Set db = Nothing
    MsgBox "SQL Server 連結資料表已更新完成"
End Function

關鍵屬性說明

• FILEDSN：指向 .dsn 檔案路徑，含驅動、伺服器資訊。
• UID/PWD：SQL Server 登入帳密。
• DATABASE：預設資料庫名稱。
• Network=DBMSSOCN：強制使用 TCP/IP 網路庫 (而非 Named Pipes)。
• dbAttachSavePWD：將密碼加密儲存於連結定義中。

微軟工程師補充

• 系統 DSN 名稱即 .dsn 檔案名 (不含路徑)。
• WSID (Workstation ID) 可留空。
• 若使用無 DSN 連線，可直接寫完整 ODBC;DRIVER=...;SERVER=...;DATABASE=...;UID=...;PWD=...。

部署建議

1. 將函數放入 Access 標準模組 (Module)。
2. 啟動表單 Form_Load 呼叫 RelinkSQLTables，實現啟動自動重連。
3. 搭配錯誤處理 (On Error Resume Next / Err.Description) 記錄失敗資訊。

一鍵重連解決方案，大幅降低 Access 前端部署與遷移的維護成本。