Access 與 SQL Server 2000 雙向資料匯入匯出實戰

利用 SQL Server 查詢分析器執行 T-SQL 分散式查詢，可直接在 Access (.mdb) 與 SQL Server 之間雙向搬移資料，無需 DTS/SSIS 圖形介面，適合腳本自動化與排程作業。

核心技術：OPENDATASOURCE / OPENROWSET

使用 Microsoft.Jet.OLEDB.4.0 提供者存取 Access 檔案，支援密碼保護的資料庫。

場景一：Access → SQL Server (匯入)

INSERT INTO sqlTableName (sField1, sField2, sField3, sField4, sField5, sField6)
SELECT 
    CONVERT(char(10), aField1),  -- 型別轉換與長度控制
    aField2, aField3, aField4, aField5, aField6
FROM OPENDATASOURCE(
    'Microsoft.Jet.OLEDB.4.0',
    'Data Source="C:\Doc\AccessDB.mdb";Jet OLEDB:Database Password=abc'
)...B04  -- Access 資料表名稱

關鍵參數說明

條件式匯入

INSERT INTO sqlTableName (...)
SELECT CONVERT(char(10), aField1), aField2, ...
FROM OPENDATASOURCE(...)...B04
WHERE aField1 = 'A' AND aField2 > 100  -- 任意 WHERE 條件

場景二：SQL Server → Access (匯出)

INSERT INTO OPENDATASOURCE(
    'Microsoft.Jet.OLEDB.4.0',
    'Data Source="C:\Doc\AccessDB.mdb";Jet OLEDB:Database Password=abc'
)...B04 (aField1, aField2, aField3, aField4, aField5, aField6)
SELECT sField1, sField2, sField3, sField4, sField5, sField6
FROM sqlTableName
WHERE sField3 = 'Active'  -- 可加條件

替代方案：BCP 工具

-- SQL Server → Access (匯出)
EXEC master..xp_cmdshell 'bcp "DBName.dbo.sqlTableName" out "C:\Data\AccessDB.mdb" -c -q -S"." -U"sa" -P"sa"'

-- Access → SQL Server (匯入) - 需先建立對應結構
EXEC master..xp_cmdshell 'bcp "DBName.dbo.sqlTableName" in "C:\Data\AccessDB.mdb" -c -q -S"." -U"sa" -P"sa"'

OPENROWSET vs BCP 比較

完整自動化腳本範例

-- 同步 Access 客戶資料到 SQL Server (每日排程)
BEGIN TRY
    BEGIN TRAN
    
    -- 1. 清空暫存表
    TRUNCATE TABLE Staging_Customers
    
    -- 2. 從 Access 匯入
    INSERT INTO Staging_Customers (CustID, CustName, Phone, Email, Region, Status)
    SELECT 
        CONVERT(int, CustID), CustName, Phone, Email, Region, Status
    FROM OPENDATASOURCE(
        'Microsoft.Jet.OLEDB.4.0',
        'Data Source="D:\Data\CRM.mdb";Jet OLEDB:Database Password=secret'
    )[Customers]
    WHERE Status <> 'Deleted'
    
    -- 3. MERGE 同步至正式表
    MERGE INTO Customers AS T
    USING Staging_Customers AS S ON T.CustID = S.CustID
    WHEN MATCHED THEN UPDATE SET 
        CustName = S.CustName, Phone = S.Phone, Email = S.Email, Region = S.Region, Status = S.Status
    WHEN NOT MATCHED THEN INSERT (CustID, CustName, Phone, Email, Region, Status)
        VALUES (S.CustID, S.CustName, S.Phone, S.Email, S.Region, S.Status)
    WHEN NOT MATCHED BY SOURCE AND T.Status <> 'Deleted' THEN UPDATE SET Status = 'Inactive';
    
    COMMIT TRAN
    PRINT '同步完成: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' 筆處理'
END TRY
BEGIN CATCH
    ROLLBACK TRAN
    PRINT '同步失敗: ' + ERROR_MESSAGE()
END CATCH

注意事項總結

1. Access .mdb 必須與 SQL Server 位於**同一台實體機器** (Jet OLEDB 不支援遠端檔案)
2. SQL Server 服務帳號需具備 .mdb 檔案 **讀寫 NTFS 權限**
3. 64 位元 SQL Server 需安裝 64 位元 Access Database Engine (ACE OLEDB 12.0+)，舊版 Jet 4.0 僅 32 位元
4. 大量資料建議分批處理，避免交易日誌暴增
5. 生產環境務必在交易中執行，確保原子性

1. 上一篇文章： SQL Server 連結 Access 資料庫異構查詢實作指南
2. 下一篇文章： SQL Server 與 Access、Excel 資料互通三大經典方案總結 (OPENROWSET、OPENDATASOURCE、BCP)

關於SqlServer

Windows Server 2008 R2 安裝 SQL Server 2005/2008 無法連線完整排查指南

前言 Windows Server 2008 R2 內建防火

IN vs UNION ALL：百萬級代理查詢的 SARG 性能實測

爭論焦點：IN 是否符合 SARG 標準？ 早期觀點： IN

解決 SQL Server 2008 R2 修改表結構「不允許儲存變更」錯誤

錯誤現象 在 SSMS 表設計師中修改結構 (如新增外來鍵、

解決安裝 SQL Server 2008 R2 / Visual Studio 2010 出現 1406 錯誤：以系統管理員身分執行

錯誤現象 環境：Windows 7 升級旗艦版後安裝