使用 SQL Server DTS 升級遷移 Access 資料庫完整指南

Access 資料庫隨業務成長常遭遇 2GB 檔案大小限制、並發效能瓶頸、安全性不足等問題，升級至 SQL Server 成為必然選擇。雖然 Access 內建「升級精靈」，但 SQL Server 的資料轉換服務 (DTS) 提供更強大的轉換控制與自動化能力。本文詳細比較兩者並示範 DTS 遷移實戰。

Access 升級精靈 (AUW) vs DTS 比較

DTS 遷移 Access 實戰步驟

1. 啟動 DTS 匯入/匯出精靈

企業管理器 → 右鍵資料庫 → 「所有工作」 → 「匯入資料」

2. 資料來源設定

• 來源：Microsoft Jet 4.0 OLE DB Provider
• 資料來源：瀏覽選取 .mdb 檔案
• 使用者名稱：Admin (預設)，密碼若有則填入

3. 目的地設定

• 目標：Microsoft OLE DB Provider for SQL Server
• 伺服器：(local) 或實際伺服器名稱
• 驗證：Windows 整合或 SQL Server 驗證
• 資料庫：目標資料庫 (可新建)

4. 複製方式選擇

• 選「從來源資料庫複製資料表與檢視表」→ 下一步
• 勾選要遷移的資料表 (可全選)

5. 關鍵：轉換設定 (DTS 核心優勢)

對每個資料表點擊「轉換」按鈕，可調整：

• 資料類型對應：Access AutoNumber → SQL INT IDENTITY(1,1)；Yes/No → BIT NOT NULL；Memo → NVARCHAR(MAX)
• 欄位對應：重新命名、刪除不需要欄位、調整順序
• 查詢篩選：改為「使用查詢指定要傳輸的資料」，撰寫 SELECT ... WHERE ... 只遷移特定資料

6. 執行與驗證

• 選「立即執行」→ 完成
• 查看執行結果：綠色勾號 = 成功，紅色叉號 = 失敗 (查看錯誤詳細資訊)

遷移後必做調整 (AUW 同樣適用)

1. Identity 欄位修正：Access AutoNumber 遷移後變成普通 INT，需手動設定 IDENTITY(1,1) 屬性
2. Yes/No 欄位修正：遷移為 BIT NOT NULL，確認業務邏輯相容 (Access -1/0 vs SQL 1/0)
3. 日期函數差異：Access Date(), Now() → SQL GETDATE()；DateAdd 語法不同
4. 查詢轉檢視表：Access 查詢需手動改寫為 T-SQL 檢視表
5. 關聯/索引/條件約束：檢查並補齊 FK、CK、預設值

DTS 進階自動化技巧

動態屬性工作：參數化連接字串

-- 全域變數設定
GlobalVariables("SourceMDB").Value = "D:\Data\Source.mdb"
GlobalVariables("TargetDB").Value = "TargetDB"

-- ActiveX Script 工作動態設定連接
Function Main()
    DTSGlobalVariables("SourceConnStr").Value = _
        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
        DTSGlobalVariables("SourceMDB").Value & ";"
    Main = DTSTaskExecResult_Success
End Function

錯誤處理流程

[資料抽取] → 成功 → [轉換/載入]
    ↓ 失敗
[記錄錯誤到 Log 表] → [發送警示郵件] → [停止/繼續]

DTS vs SSIS (SQL 2005+) 選擇建議

• SQL Server 2000 環境：DTS 唯一選擇
• SQL Server 2005/2008：強制使用 SSIS (DTS 相容模式僅為過渡)
• SSIS 優勢：.NET 程式化、資料流引擎重寫、平行處理、封裝部署模型、內建資料剖析

遷移專案檢核清單

1. [ ] 完整備份 Access .mdb (含安全性檔 .mdw)
2. [ ] 清查所有資料表、查詢、表單、報表、巨集、模組清單
3. [ ] 識別 VBA 程式碼與 Access 專有函數 (Nz, IIf, DLookup, DateDiff)
4. [ ] 建立資料類型對應對照表
5. [ ] 測試環境完整跑一次 DTS 遷移
6. [ ] 資料筆數比對、抽樣驗證、應用程式 UAT
7. [ ] 制定回滾方案 (保留 Access 原檔、記錄 DTS 套件)