許多中小企業從 Access 起家，隨著業務增長需遷移至 SQL Server。本文詳述 Access 2000 到 SQL Server 2000 的標準遷移流程，重點解析資料類型映射陷阱、主鍵身分識別重建、日期函數差異處理等核心難點，並提供遷移後的效能優化清單。
Driver do Microsoft Access (*.mdb) 已安裝
控制台 → 管理工具 → 資料來源 (ODBC) → 系統 DSN 標籤 → 新增
→ 選擇 "Driver do Microsoft Access (*.mdb)"
→ 資料來源名稱: Access_Source（自定義）
→ 選擇資料庫: 瀏覽定位到 .mdb 檔案
→ 確定完成
企業管理器 → 右鍵「資料庫」 → 新增資料庫
名稱: TargetDB（與 Access 檔名對應）
資料檔: 100MB 起，自動增長 10%
記錄檔: 50MB 起，自動增長 10%
復原模式: 簡單（遷移期間減少記錄增長）
Access_Source DSNTargetDBDTS 導入僅完成結構與資料的物理搬移，邏輯層面的語意差異必須人工修正：
| Access 類型 | DTS 預設映射 | 問題描述 | 必須手工修正為 |
|---|---|---|---|
| 自動編號 (長整數) | INT NOT NULL | 遺失 IDENTITY 屬性 | INT IDENTITY(1,1) PRIMARY KEY |
| 是/否 (Yes/No) | BIT NOT NULL | 無法直接用於 CHECK 約束、預設值處理異常 | BIT NULL DEFAULT 0 或 CHAR(1) CHECK IN ('Y','N') |
| 日期/時間 | DATETIME | 日期函數語法差異大 | 保留 DATETIME，但需重寫所有日期運算 |
| 備忘錄 | TEXT | TEXT 類型即將淘汰，功能受限 | NVARCHAR(MAX) (SQL 2005+) / NTEXT |
| OLE 物件 | IMAGE | 二進制大物件處理效能差 | VARBINARY(MAX) 或檔案系統存儲+路徑引用 |
-- 針對所有無主鍵且有 INT NOT NULL 欄位的表
DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql += '
ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']
DROP COLUMN [' + COLUMN_NAME + '];
ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']
ADD [' + COLUMN_NAME + '] INT IDENTITY(1,1) PRIMARY KEY;'
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN INFORMATION_SCHEMA.TABLES t ON c.TABLE_NAME = t.TABLE_NAME
WHERE c.DATA_TYPE = 'int' AND c.IS_NULLABLE = 'NO'
AND t.TABLE_TYPE = 'BASE TABLE'
AND NOT EXISTS (
SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
WHERE tc.TABLE_NAME = t.TABLE_NAME AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
);
EXEC sp_executesql @sql;
| Access 函數 | SQL Server 對應寫法 | 範例 |
|---|---|---|
| Date() | GETDATE() / CAST(GETDATE() AS DATE) | 當前日期 |
| Now() | GETDATE() | 當前日期時間 |
| DateAdd("d", 30, [DateField]) | DATEADD(day, 30, DateField) | 加 30 天 |
| DateDiff("d", [StartDate], [EndDate]) | DATEDIFF(day, StartDate, EndDate) | 天數差 |
| Format([DateField], "yyyy-mm-dd") | CONVERT(VARCHAR(10), DateField, 120) | 格式化輸出 |
| Year()/Month()/Day() | YEAR()/MONTH()/DAY() | 同名函數 |
| IIf(IsNull([DateField]), ..., ...) | CASE WHEN DateField IS NULL THEN ... ELSE ... END | 條件判斷 |
EXEC sp_updatestats; 確保查詢優化器有最新統計資訊DBCC DBREINDEX(@DBName, '', 90); 消除遷移產生的碎片遷移專家建議：採用「分批遷移、雙寫驗證、灰度切換」策略。先遷移唯讀查詢類應用，驗證 1 週無誤後，再遷移核心交易系統。保留 Access 系統作為回滾方案至少 1 個月。
