SQL Server 遷移 Access 完整指南：結構轉換、連線字串、參數化與語法差異對照表

前言

將 SQL Server 應用遷移至 Access (Jet/ACE OLEDB) 是典型的「縮容遷移」，常見於小型專案、離線桌面應用或教學環境。遷移核心在於：**資料型別對應、保留字衝突、連線提供者切換、ADO.NET 物件替換、SQL 語法差異** 五大面向。本文提供完整對照表與實戰修改清單。

一、遷移前置作業

1. 在 Access 建立空資料庫 (.mdb Jet 4.0 / .accdb ACE 12.0)。
2. 依型別對照表建立資料表結構（見下表）。
3. **關鍵**：Access 保留字極多（Password, Date, Time, Name, Level, Key, Order, Group, Description...），欄位名**務必避開**，否則 SQL 會語法錯誤。若已存在，需用 [Password] 方括號包裹。

二、資料型別對照表 (SQL Server → Access)

三、Access DDL 型別修改腳本 (ALTER TABLE)

ALTER TABLE MyTable ALTER COLUMN Col1 Byte;        -- Number (Byte)
ALTER TABLE MyTable ALTER COLUMN Col1 Long;        -- Number (Long Integer)
ALTER TABLE MyTable ALTER COLUMN Col1 Short;       -- Number (Integer)
ALTER TABLE MyTable ALTER COLUMN Col1 Single;      -- Number (Single)
ALTER TABLE MyTable ALTER COLUMN Col1 Double;      -- Number (Double)
ALTER TABLE MyTable ALTER COLUMN Col1 Currency;    -- Currency
ALTER TABLE MyTable ALTER COLUMN Col1 Char;        -- Text
ALTER TABLE MyTable ALTER COLUMN Col1 Text(50);    -- Text (size 50)
ALTER TABLE MyTable ALTER COLUMN Col1 Binary;      -- Binary
ALTER TABLE MyTable ALTER COLUMN Col1 Counter;     -- AutoNumber
ALTER TABLE MyTable ALTER COLUMN Col1 Memo;        -- Memo
ALTER TABLE MyTable ALTER COLUMN Col1 DateTime;    -- Date/Time

四、ASP.NET 遷移修改清單

4.1 連線字串 (web.config)

• |DataDirectory| 解析為 App_Data 目錄。
• ACE 12.0 需目標機安裝 Access Database Engine 2010/2016 Redistributable (x64/x86 需與 IIS 應用程式集區位元數一致)。

4.2 命名空間與物件全面替換

4.3 參數型別對應 (SqlDbType → OleDbType)

// SqlDbType.NVarChar  → OleDbType.LongVarWChar (或 VarWChar)
// SqlDbType.Int       → OleDbType.Integer
// SqlDbType.Bit       → OleDbType.Boolean
// SqlDbType.DateTime  → OleDbType.DBTimeStamp

**關鍵差異**：OleDbParameter **不支援命名參數對應順序**，參數集合加入順序**必須與 SQL 陳述式中參數出現順序完全一致**。

4.4 SQL 語法差異對照表

4.5 Access 分頁替代方案 (無 ROW_NUMBER)

-- 參數：@startIndex (起始 1-based), @pageSize
-- 方案：雙重 TOP + LEFT JOIN 排除法
SELECT a.* 
FROM (SELECT TOP @pageSize * FROM (SELECT TOP @startIndex + @pageSize - 1 * FROM MyTable ORDER BY Id) t ORDER BY Id DESC) a
ORDER BY a.Id;

五、Access 資料型別詳細說明

• Text：最大 255 字元，預設 50，僅儲存實際輸入字元，可建索引/排序。
• Memo：長文字，上限 64KB+ (ACE 更大)，**不可建索引/排序**，搜尋較慢。
• Number：透過 FieldSize 屬性細分 Byte/Integer/Long/Single/Double/Decimal/Replication ID。
• Date/Time：8 位元組，儲存日期+時間。
• Currency：固定 4 位小數，精確度 15+4 位，自動顯示貨幣符號。
• AutoNumber：自動遞增/隨機，刪除不重用，永久綁定記錄。
• Yes/No：布林，顯示為 Yes/No、True/False、On/Off。
• OLE Object：嵌入/連結 Word/Excel/圖片/聲音，最大 1GB (受磁碟限制)。
• Hyperlink：儲存超連結 (顯示文字、路徑、書籤)。
• Attachment (ACE only)：多檔案附件，取代 OLE Object。

六、遷移檢核清單

1. [ ] 所有資料表/欄位名稱檢查保留字衝突，必要時加方括號 [Name]。
2. [ ] 型別對應表逐欄驗證，特別注意 Memo 不可索引、AutoNumber 行為差異。
3. [ ] 連線字串切換至正確 Provider (Jet 4.0 / ACE 12.0)。
4. [ ] 專案引用 System.Data.OleDb，全域搜尋替換 Sql* → OleDb* 類別。
5. [ ] 參數集合順序與 SQL 參數順序一一對應。
6. [ ] 所有 SQL 陳述式套用語法差異表修正 (<>、IS NULL、TOP、@@IDENTITY、ROW_NUMBER)。
7. [ ] 測試分頁、大量資料、並發存取、交易處理。
8. [ ] 部署目標機安裝對應版本 Access Database Engine。