SQL Server 連結 Access 資料庫異構查詢實作指南

SQL Server 透過「連結伺服器」機制配合 OLE DB Provider for ODBC，可實現與 Microsoft Access 資料庫的異構整合。本文記錄在 Windows 2000 Server + SQL Server 2000 + Access 環境下的完整設定與查詢語法。

前置需求

• 作業系統：Windows 2000 Server (繁體中文版)
• 資料庫軟體：SQL Server 2000 (英文版)、Microsoft Access
• 兩者安裝於同一台實體機器

步驟 1：設定 Windows ODBC 系統資料來源

1. 開啟「控制台」→「系統管理工具」→「資料來源 (ODBC)」
2. 選擇「系統 DSN」頁籤 (服務帳號需可見)，點擊「新增」
3. 選擇 Microsoft Access Driver (*.mdb)
4. 在「ODBC Microsoft Access Setup」視窗中：
   • 資料來源名稱：自訂，如 ACCESSTEST
   • 選擇資料庫：瀏覽選取目標 .mdb 檔案路徑
5. 完成並點擊「測試」驗證連線

步驟 2：建立 SQL Server 連結伺服器

1. 企業管理器 → 安全性 → 連結伺服器 → 右鍵「新增連結伺服器」
2. 「一般」頁籤：
   • 連結伺服器名稱：自訂，如 LINK_ACCESS
   • 伺服器類型：其他資料來源
   • 提供者：Microsoft OLE DB Provider for ODBC Drivers
   • 資料來源：輸入步驟 1 的 ODBC 名稱 (ACCESSTEST)
   • 提供者字串：User ID=admin;Password=password (Access 預設帳號 admin，無密碼可留空)
3. 「安全性」頁籤：選「使用此安全性內容」，輸入 Access 資料庫帳密
4. 「伺服器選項」頁籤：預設即可 (資料存取、RPC、排序規則等)

步驟 3：驗證與四部分命名查詢

企業管理器展開連結伺服器 → 資料表，可見 Access 所有資料表 (僅中繼資料)。實際查詢需在查詢分析器執行：

語法格式

[連結伺服器名稱]...[Access使用者].[資料表名稱]

Access 無結構描述/使用者概念，中間留空：

SELECT * FROM [LINK_ACCESS]...[Customers]
SELECT * FROM [LINK_ACCESS]...[Orders] WHERE OrderDate > '2024-01-01'

OPENQUERY 替代寫法 (推薦複雜查詢)

SELECT * FROM OPENQUERY(LINK_ACCESS, 'SELECT * FROM Customers WHERE Country = "USA"')

• 查詢在 Access 引擎端執行，僅回傳結果，減少網路傳輸
• 可使用 Access 原生 SQL 語法 (如 IIf、DLookup)

常見問題與解決

進階應用：分散式查詢 (無連結伺服器)

-- OPENDATASOURCE 單次查詢
SELECT * FROM OPENDATASOURCE(
    'Microsoft.Jet.OLEDB.4.0',
    'Data Source="C:\Data\Northwind.mdb";User ID=admin;Password='
)...[Customers]

-- OPENROWSET 替代
SELECT * FROM OPENROWSET(
    'Microsoft.Jet.OLEDB.4.0',
    'C:\Data\Northwind.mdb';'admin';'',
    'SELECT * FROM Customers'
)

安全性注意事項

• 啟用 Ad Hoc Distributed Queries 伺服器選項需謹慎 (預設關閉)
• Access 檔案層級權限 (檔案系統 ACL) 必須允許 SQL Server 服務帳號讀寫
• 避免在連接字串硬編碼密碼，考慮 Windows 整合驗證或儲存認證