開發 WinForms 個人記帳軟體，初期為免安裝資料庫服務選用 **Access (ACE/Jet)** 做本地儲存。開發過程踩中多個與 SQL Server 差異導致的坑，記錄於此供參考。
現象：簡單 INSERT INTO Users (PassWord) VALUES ('123') 執行無錯誤但資料未寫入 / 拋出語法錯誤。
原因：PassWord 是 **Access 保留關鍵字** (Jet 4.0/ACE)。SQL Server 允許用方括號 [PassWord] 逸出，Access **若未加方括號直接報錯/失效**。
解決**：所有 DDL/DML **一律用方括號包裹識別碼** [PassWord]，或改名 Pwd / UserPassword。
| PASSWORD | USER | GROUP | LEVEL | KEY | INDEX | ORDER | DATE | TIME | YEAR | MONTH | DAY |
| SELECT | INSERT | UPDATE | DELETE | CREATE | DROP | TABLE | VIEW | PROCEDURE | FUNCTION | TRIGGER | CONSTRAINT |
| IDENTITY | AUTOINCREMENT | COUNTER | MEMO | OLEOBJECT | BINARY | TEXT | NOTE | LONG | SHORT | SINGLE | DOUBLE |
建議**：開發時開啟 ANSI_QUOTES 相容模式或統一用方括號，命名避開上述詞彙。
| 資料庫型別 | SQL Server 對應 .NET 型別 | Access (OleDb) 對應 .NET 型別 |
|---|---|---|
| DATETIME / SMALLDATETIME | DateTime (DbType.DateTime) | DateTime **但參數必須指定 DbType.DateTime** |
| DATE (Access 無此型別) | DateTime / DateOnly (.NET 6+) | 無 |
| TIMESTAMP (Rowversion) | Byte[] | 無 |
實踐踩坑**：用 OleDbParameter 設 DbType.DateTime 傳入 DateTime.Now 卻報型別不匹配。
**根因**：Access OleDb 提供者對參數型別推斷極嚴格，需顯式指定 OleDbType.DBTimeStamp 或確保 Value 為 DateTime 非 String。
// 正確寫法
cmd.Parameters.Add("@dt", OleDbType.DBTimeStamp).Value = DateTime.Now;
// 或
var p = cmd.Parameters.Add("@dt", OleDbType.Date);
p.Value = DateTime.Today; // 僅日期
| 操作 | SQL Server | Access (Jet/ACE) |
|---|---|---|
| 建表指定種子/步長 | ID INT IDENTITY(1000,10) | ID COUNTER (1000, 10) 或 AUTOINCREMENT (1000, 10) |
| 事後修改種子/步長 | DBCC CHECKIDENT ('Table', RESEED, 2000) | ALTER TABLE Table ALTER COLUMN ID COUNTER (2000, 50) |
| 重置從 1 開始 | DBCC CHECKIDENT ('Table', RESEED, 0) | ALTER TABLE Table ALTER COLUMN ID COUNTER (1, 1) |
警告**：Access 修改 COUNTER **不檢查唯一性**，若表內已有資料極易產生重複主鍵，**務必先備份、清空表或設為主鍵/唯一索引強制檢查**。
| 功能 | SQL Server (T-SQL) | Access (Jet/ACE SQL) |
|---|---|---|
| 取前 N 筆 | SELECT TOP (10) * FROM T | SELECT TOP 10 * FROM T (無括號) |
| 字串串接 | + 或 CONCAT() | & (如 "A" & "B") |
| 當前日期 | GETDATE() / SYSDATETIME() | NOW() / DATE() |
| 條件判斷 | CASE WHEN ... THEN ... ELSE ... END | IIF(cond, trueVal, falseVal) 或 SWITCH |
| 參數化查詢佔位符 | @ParamName | ? (位置參數，順序必須對應) |
| LIMIT/OFFSET | OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY (2012+) | **不支援**，需用子查詢 TOP 模擬 |
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\App.mdb;Persist Security Info=False;AttachDbFilename 掛載 .mdf。連接字串：Server=(localdb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\App.mdf;Integrated Security=True;| 場景 | 推薦 | 理由 |
|---|---|---|
| 單機、單用戶、< 100MB、免安裝、簡單 CRUD | Access / SQLite | 零配置、檔案即資料庫、複製即備份 |
| 多用戶並發、> 100MB、需備份/還原/複寫/安全性/預存程序 | SQL Server Express / LocalDB / PostgreSQL | 真正的伺服器架構、ACID、鎖管理、擴展性 |
| 跨平台、.NET Core / 容器化、開源生態 | PostgreSQL / SQLite | Linux 友善、功能強大、無授權成本 |
A: ADD, ALL, ALPHANUMERIC, ALTER, AND, ANY, APPLICATION, AS, ASC, ASSISTANT, AUTOINCREMENT, AVG
B: BETWEEN, BINARY, BIT, BOOLEAN, BY, BYTE
C: CHAR, CHARACTER, COLUMN, COMPACTDATABASE, CONSTRAINT, CONTAINER, COUNT, COUNTER, CREATE, CREATEDATABASE, CREATEFIELD, CREATEGROUP, CREATEINDEX, CREATEOBJECT, CREATEPROPERTY, CREATERELATION, CREATETABLEDEF, CREATEUSER, CREATEWORKSPACE, CURRENCY, CURRENTUSER
D: DATABASE, DATE, DATETIME, DELETE, DESC, DESCRIPTION, DISALLOW, DISTINCT, DISTINCTROW, DOCUMENT, DOUBLE, DROP
E: ECHO, ELSE, END, EQV, ERROR, EXISTS, EXIT
F: FALSE, FIELD, FIELDS, FILLCACHE, FLOAT, FLOAT4, FLOAT8, FOREIGN, FORM, FORMS, FROM, FULL, FUNCTION
G: GENERAL, GETOBJECT, GETOPTION, GOTOPAGE, GROUP, GROUP BY, GUID
H: HAVING
I: IDLE, IEEEDOUBLE, IEEESINGLE, IF, IGNORE, IMP, IN, INDEX, INDEXES, INNER, INSERT, INSERTTEXT, INT, INTEGER, INTEGER1, INTEGER2, INTEGER4, INTO, IS
J: JOIN
K: KEY
L: LASTMODIFIED, LEFT, LEVEL, LIKE, LOGICAL, LOGICAL1, LONG, LONGBINARY, LONGTEXT
M: MACRO, MATCH, MAX, MIN, MOD, MEMO, MODULE, MONEY, MOVE
N: NAME, NEWPASSWORD, NO, NOT, NOTE, NULL, NUMBER, NUMERIC
O: OBJECT, OLEOBJECT, OFF, ON, OPENRECORDSET, OPTION, OR, ORDER, ORIENTATION, OUTER, OWNERACCESS
P: PARAMETER, PARAMETERS, PARTIAL, PERCENT, PIVOT, PRIMARY, PROCEDURE, PROPERTY
Q: QUERIES, QUERY, QUIT
R: REAL, RECALC, RECORDSET, REFERENCES, REFRESH, REFRESHLINK, REGISTERDATABASE, RELATION, REPAINT, REPAIRDATABASE, REPORT, REPORTS, REQUERY, RIGHT
S: SCREEN, SECTION, SELECT, SET, SETFOCUS, SETOPTION, SHORT, SINGLE, SMALLINT, SOME, SQL, STDEV, STDEVP, STRING, SUM
T: TABLE, TABLEDEF, TABLEDEFS, TABLEID, TEXT, TIME, TIMESTAMP, TOP, TRANSFORM, TRUE, TYPE
U: UNION, UNIQUE, UPDATE, USER
V: VALUE, VALUES, VAR, VARP, VARBINARY, VARCHAR
W: WHERE, WITH, WORKSPACE
X: XOR
Y: YEAR
Z: YES, YESNO
