SQL Server 異構數據整合：鏈接服務器聯邦查詢 Access 與 SQL Server 聯合分析

業務常面臨核心數據在 SQL Server，輔助/歷史數據在 Access (.mdb/.accdb) 的混合架構。通過 鏈接服務器 將 Access 掛載為 SQL Server 的遠程數據源，可在 T-SQL 中直接跨引擎 JOIN、UNION、APPLY，無需 ETL 落地中間表。本文給出完整配置與查詢示例。

創建 Access 鏈接服務器腳本

USE master;
GO

-- 1. 添加鏈接服務器（Jet OLE DB Provider）
EXEC sp_addlinkedserver
    @server = 'VALINKMDB',                    -- 邏輯名
    @srvproduct = '',
    @provider = 'Microsoft.Jet.OLEDB.4.0',    -- Access 2000-2003 (.mdb)
    -- @provider = 'Microsoft.ACE.OLEDB.12.0', -- Access 2007+ (.accdb)
    @datasrc = 'C:\Data\McMo.mdb';            -- 物理文件路徑

-- 2. 配置登錄映射（無密碼或統一映射）
EXEC sp_addlinkedsrvlogin
    @rmtsrvname = 'VALINKMDB',
    @useself = 'FALSE',
    @locallogin = NULL,
    @rmtuser = 'Admin',        -- Access 默認用戶
    @rmtpassword = '';         -- 無密碼留空，有密碼填寫

-- 3. 啟用數據訪問（允許 OPENQUERY/四部分命名查詢）
EXEC sp_serveroption 'VALINKMDB', 'Data Access', 'True';

-- 4. 可選：啟用排序規則兼容（提升 JOIN 性能）
EXEC sp_serveroption 'VALINKMDB', 'Collation Compatible', 'True';

驗證連接與查詢

-- 測試連接：列出 Access 所有表
EXEC sp_tables_ex 'VALINKMDB';

-- 直接查詢 Access 表（四部分命名）
SELECT * FROM VALINKMDB...Customers;  -- 三點符號：Server.Database.Schema.Object，Access 無 Schema 故中間空

-- 跨引擎 JOIN：SQL Server 訂單表關聯 Access 客戶表
SELECT o.OrderID, o.OrderDate, c.CustomerName, c.City
FROM SQLServerDB.dbo.Orders o
INNER JOIN VALINKMDB...Customers c
    ON o.CustomerID = c.CustomerID
WHERE o.OrderDate >= '2024-01-01';

Access 密碼保護文件連接

-- 修改 @datasrc 包含密碼（Jet 4.0 支持）
@datasrc = 'C:\Data\Secured.mdb;Jet OLEDB:Database Password=MySecretPwd;'

-- 或 ACE Provider (Access 2007+)
@provider = 'Microsoft.ACE.OLEDB.12.0'
@datasrc = 'C:\Data\Secured.accdb;Jet OLEDB:Database Password=MySecretPwd;'

性能優化關鍵點

應用層調用（ASP.NET / .NET Core 示例）

using (var conn = new SqlConnection(sqlConnStr))
using (var cmd = conn.CreateCommand())
{
    cmd.CommandText = @"
        SELECT o.OrderID, c.CustomerName
        FROM dbo.Orders o
        INNER JOIN VALINKMDB...Customers c ON o.CustomerID = c.CustomerID
        WHERE o.OrderDate >= @StartDate";
    cmd.Parameters.AddWithValue("@StartDate", DateTime.Today.AddMonths(-1));
    
    conn.Open();
    using (var reader = cmd.ExecuteReader())
    {
        while (reader.Read()) { /* 處理行 */ }
    }
}

連接字符串無需特別配置，鏈接服務器對應用透明，同樣走 TDS 協議。

常見問題排查

總結

鏈接服務器聯邦查詢實現了 SQL Server 與 Access 的即時、透明、雙向數據整合，適合「核心交易在 SQL Server、邊緣數據在 Access」的混合架構過渡期。長期規劃應將 Access 數據遷移至 SQL Server/PostgreSQL 等標準關係型資料庫，消除異構引擎維護成本與性能天花板。

1. 上一篇文章： SQL Server 將存儲過程結果集插入臨時表：OPENQUERY + EXEC 模式
2. 下一篇文章： 跨平台 MySQL 導出 Excel 工具：Perl + Spreadsheet::WriteExcel::Big 實現大文件生成

MySQL 資料庫相關知識

MySQL 複製加密實戰：SSH 隧道替代 SSL 實現主從傳輸加密

為什麼需要加密複製？ MySQL 原生複製協議明文傳輸，跨公

MySQL 刪除資料庫操作教學：mysqladmin 與 PHP 兩種方式

刪除 MySQL 資料庫需具備特殊權限（通常為 root）。

Windows 下使用批處理腳本實現 MySQL 自動備份與清理

需求場景 在 Windows Server 2003 環境下

MySQL 子查詢完全指南：類型、優化與實戰技巧

深入理解子查詢機制與效能調優 子查詢（內部查詢）嵌套於外部查