簡介這份資源是面向VB.Net初學(xué)者與桌面應(yīng)用開發(fā)者的Access數(shù)據(jù)庫操作示例工程圍繞ADO.Net框架講解如何連接Access數(shù)據(jù)庫并完成增刪改查。內(nèi)容涵蓋OleDbConnection連接字符串配置、OleDbCommand執(zhí)行SQL、OleDbDataReader讀取數(shù)據(jù)以及OleDbDataAdapter配合DataSet進(jìn)行數(shù)據(jù)填充與綁定的完整思路適合需要快速上手?jǐn)?shù)據(jù)庫交互的開發(fā)者參考。壓縮包共18個文件約47KB包含vb與vbproj項目源碼、sln解決方案、mdb數(shù)據(jù)庫文件、resx與resources資源文件、xml配置及exe可執(zhí)行程序等構(gòu)成一個可直接運行的完整示例工程。目前已有228人學(xué)習(xí)下載。通過這份示例讀者可以對照源碼理解連接建立、參數(shù)化查詢、資源釋放等關(guān)鍵環(huán)節(jié)并借助VS調(diào)試工具排查問題為后續(xù)開發(fā)數(shù)據(jù)驅(qū)動的Windows應(yīng)用打下基礎(chǔ)。1. Access 數(shù)據(jù)庫操作示例從單機文件到增刪改查的完整落地Access 數(shù)據(jù)庫操作示例本質(zhì)上是在講一件事如何用最輕量的方式把一個.accdb或.mdb文件當(dāng)成真正的數(shù)據(jù)庫來用而不是把它當(dāng)成一個高級 Excel。很多人第一次接觸 Access 是在做課程設(shè)計或者小型管理系統(tǒng)表建好了、窗體拖出來了但一到寫查詢、做批量更新、處理并發(fā)就翻車。問題不在工具本身而在于沒有把 Access 當(dāng)成一個有 SQL 方言、有事務(wù)邊界、有連接模型的數(shù)據(jù)庫來看待。這篇筆記面向三類人一是要用 Access 快速搭一個本地數(shù)據(jù)管理工具的后端開發(fā)者二是需要把 Access 里的數(shù)據(jù)接進(jìn) C#、Python 或報表工具的人三是被數(shù)據(jù)庫增刪改查這四個字困住、只會點鼠標(biāo)不會寫語句的新手。我會從表結(jié)構(gòu)設(shè)計講到 SQL 寫法再講到連接方式、參數(shù)化查詢、批量操作和常見報錯排查每一步都給可復(fù)制的代碼和參數(shù)說明。Access 不是玩具它在單機和小團(tuán)隊場景下的性價比比很多人想象的高得多。2. 先把表結(jié)構(gòu)和字段類型定下來Access 的數(shù)據(jù)類型與建表語句2.1 Access 的字段類型和常見誤用Access 的字段類型和 MySQL、SQLite 不完全一樣直接照搬會踩坑。最常見的幾個類型是TEXT短文本最長 255 字符、MEMO長文本實際對應(yīng)LONGTEXT、INTEGER長整型、DOUBLE雙精度、CURRENCY貨幣精度高、DATETIME日期時間、YESNO布爾、COUNTER自增主鍵。很多人把身份證號、手機號存成INTEGER結(jié)果前導(dǎo)零丟失、超出范圍把備注存成TEXT超過 255 字符直接被截斷。正確做法是編號類字段一律用TEXT金額用CURRENCY長描述用MEMO。另一個高頻問題是主鍵。Access 里可以用COUNTER做自增主鍵也可以用TEXT做業(yè)務(wù)主鍵。如果后續(xù)要和其他系統(tǒng)同步建議用TEXT主鍵加唯一索引避免自增 ID 在合并數(shù)據(jù)時沖突。建表時最好顯式聲明NOT NULL和默認(rèn)值A(chǔ)ccess 的默認(rèn)值語法是DEFAULT但只對新增記錄生效歷史數(shù)據(jù)不會回填。2.2 用 SQL 建表的完整示例下面這段 SQL 可以在 Access 的查詢設(shè)計視圖里切換到 SQL 模式直接執(zhí)行也可以通過 ADO 或 ODBC 執(zhí)行。注意 Access 的CREATE TABLE不支持IF NOT EXISTS重復(fù)執(zhí)行會報錯所以腳本里要先判斷表是否存在。-- 先刪除舊表如果存在避免重復(fù)建表報錯 DROP TABLE 員工信息; -- 創(chuàng)建員工信息表 CREATE TABLE 員工信息 ( emp_id TEXT(20) NOT NULL, -- 工號業(yè)務(wù)主鍵用文本避免前導(dǎo)零丟失 emp_name TEXT(50) NOT NULL, -- 姓名 dept_code TEXT(10), -- 部門編碼 salary CURRENCY, -- 薪資貨幣類型精度高 hire_date DATETIME, -- 入職日期 remark MEMO, -- 備注長文本 is_active YESNO DEFAULT YES, -- 是否在職默認(rèn)是 CONSTRAINT pk_emp PRIMARY KEY (emp_id) ); -- 給部門編碼建索引加速按部門查詢 CREATE INDEX idx_dept ON 員工信息 (dept_code);這段代碼里TEXT(20)的 20 是字符長度上限不是字節(jié)數(shù)CURRENCY在 Access 里實際是 8 字節(jié)定點數(shù)適合金額YESNO在 SQL 里可以用YES/NO或TRUE/FALSE但 Access 界面顯示為復(fù)選框。CONSTRAINT pk_emp PRIMARY KEY顯式命名主鍵方便后續(xù)用ALTER TABLE引用。索引單獨用CREATE INDEX建不要寫在CREATE TABLE里面Access 不支持內(nèi)聯(lián)索引定義。注意Access 的 SQL 方言對保留字很敏感字段名如果叫date、password、level必須用方括號包起來比如[date]。我一般建議字段名加前綴或改用hire_date這種明確寫法省得后面到處加括號。2.3 用 ADO 在 C# 里建表和改結(jié)構(gòu)如果是在 C# 項目里操作 Access推薦用System.Data.OleDb它是 .NET 里最穩(wěn)的 Access 驅(qū)動。下面這段代碼演示如何用 ADO 執(zhí)行建表語句并檢查表是否已存在。using System.Data.OleDb; string connStr ProviderMicrosoft.ACE.OLEDB.12.0;Data SourceD:\data\hr.accdb;; using (OleDbConnection conn new OleDbConnection(connStr)) { conn.Open(); // 先查系統(tǒng)表判斷目標(biāo)表是否存在 var checkCmd new OleDbCommand( SELECT COUNT(*) FROM MSysObjects WHERE Name員工信息 AND Type1, conn); int exists (int)checkCmd.ExecuteScalar(); if (exists 0) { string ddl CREATE TABLE 員工信息 ( emp_id TEXT(20) NOT NULL, emp_name TEXT(50) NOT NULL, dept_code TEXT(10), salary CURRENCY, hire_date DATETIME, remark MEMO, is_active YESNO, CONSTRAINT pk_emp PRIMARY KEY (emp_id) ); new OleDbCommand(ddl, conn).ExecuteNonQuery(); } conn.Close(); }連接字符串里的ProviderMicrosoft.ACE.OLEDB.12.0對應(yīng).accdb格式如果是老的.mdb要用Microsoft.Jet.OLEDB.4.0。MSysObjects是 Access 的系統(tǒng)表Type1表示本地表Type6表示鏈接表。查MSysObjects需要權(quán)限某些環(huán)境下會被拒絕備選方案是直接SELECT TOP 1 * FROM 表名然后捕獲異常。ExecuteNonQuery返回受影響行數(shù)建表語句返回 0 是正常的。3. 增刪改查四種操作參數(shù)化 SQL 與事務(wù)邊界3.1 插入數(shù)據(jù)參數(shù)化避免注入和轉(zhuǎn)義問題Access 的 SQL 里字符串用單引號包裹日期用#包裹比如#2024-01-15#。如果直接拼接字符串遇到姓名里有單引號比如 OBrien就會報錯更嚴(yán)重的是被注入。參數(shù)化查詢是唯一正確的做法。OleDb 的參數(shù)用?占位順序必須和Parameters.Add的順序一致這點和 SQL Server 的name不同容易翻車。string sql INSERT INTO 員工信息 (emp_id, emp_name, dept_code, salary, hire_date, remark, is_active) VALUES (?, ?, ?, ?, ?, ?, ?); using (OleDbCommand cmd new OleDbCommand(sql, conn)) { cmd.Parameters.AddWithValue(?, E1001); cmd.Parameters.AddWithValue(?, 張三); cmd.Parameters.AddWithValue(?, D01); cmd.Parameters.AddWithValue(?, 12500.00m); cmd.Parameters.AddWithValue(?, new DateTime(2024, 1, 15)); cmd.Parameters.AddWithValue(?, 試用期三個月); cmd.Parameters.AddWithValue(?, true); int rows cmd.ExecuteNonQuery(); }AddWithValue的順序就是?的順序?qū)戝e一個位置數(shù)據(jù)就串列了。金額用decimal類型傳入不要用double否則可能出現(xiàn) 12500.000000001 這種精度問題。日期直接傳DateTime對象OleDb 會自動轉(zhuǎn)成 Access 的日期字面量。布爾值傳true/falseAccess 會存成-1/0查詢時用YESNO字段直接比較TRUE即可。3.2 批量插入用事務(wù)把一千條壓進(jìn)一秒單條插入一千次每次開一個OleDbCommand在 Access 上大概要十幾秒。正確做法是開事務(wù)復(fù)用同一個命令對象只改參數(shù)值。Access 對事務(wù)的支持是完整的OleDbTransaction可以顯著提升批量寫入性能。using (OleDbTransaction tx conn.BeginTransaction()) { string sql INSERT INTO 員工信息 (emp_id, emp_name, dept_code, salary, hire_date) VALUES (?, ?, ?, ?, ?); using (OleDbCommand cmd new OleDbCommand(sql, conn, tx)) { // 預(yù)先添加參數(shù)占位后續(xù)只改值 cmd.Parameters.Add(?, OleDbType.VarWChar); cmd.Parameters.Add(?, OleDbType.VarWChar); cmd.Parameters.Add(?, OleDbType.VarWChar); cmd.Parameters.Add(?, OleDbType.Currency); cmd.Parameters.Add(?, OleDbType.Date); for (int i 0; i 1000; i) { cmd.Parameters[0].Value E (2000 i); cmd.Parameters[1].Value 員工 i; cmd.Parameters[2].Value D0 (i % 5 1); cmd.Parameters[3].Value 8000 i * 10; cmd.Parameters[4].Value DateTime.Today.AddDays(-i); cmd.ExecuteNonQuery(); } } tx.Commit(); }關(guān)鍵點是OleDbCommand構(gòu)造時傳入tx否則命令不在事務(wù)里回滾無效。參數(shù)只Add一次循環(huán)里改Value避免反復(fù)解析 SQL。一千條數(shù)據(jù)用這種方式大概 0.5 到 1 秒比逐條提交快一個數(shù)量級。如果中途出錯tx.Rollback()可以全部撤銷這就是事務(wù)的后悔藥。3.3 查詢、更新和刪除的寫法差異查詢用OleDbDataReader逐行讀適合大數(shù)據(jù)量小數(shù)據(jù)量用OleDbDataAdapter填DataTable更方便。更新和刪除的 SQL 語法和標(biāo)準(zhǔn) SQL 基本一致但 Access 不支持UPDATE ... FROM和DELETE ... USING多表關(guān)聯(lián)更新要寫成子查詢。-- 查詢按部門篩選在職員工 SELECT emp_id, emp_name, salary FROM 員工信息 WHERE dept_code ? AND is_active TRUE ORDER BY salary DESC; -- 更新給指定部門全員漲薪 10% UPDATE 員工信息 SET salary salary * 1.1 WHERE dept_code ? AND is_active TRUE; -- 刪除軟刪除把離職員工標(biāo)記為非在職 UPDATE 員工信息 SET is_active FALSE WHERE emp_id ?; -- 物理刪除慎用 DELETE FROM 員工信息 WHERE emp_id ? AND is_active FALSE;Access 的UPDATE支持表達(dá)式salary * 1.1會按行計算。DELETE不帶WHERE會清空整表且 Access 沒有TRUNCATE清空大表很慢。我一般建議用軟刪除加一個is_active字段查詢時過濾既保留歷史又避免誤刪。如果確實要物理刪除先SELECT COUNT(*)確認(rèn)影響行數(shù)再執(zhí)行。注意Access 的ORDER BY對中文默認(rèn)按拼音排序如果按筆畫排序需要在界面里設(shè)置SQL 層面改不了。涉及中文排序的業(yè)務(wù)建議在應(yīng)用層用StringComparer處理別依賴數(shù)據(jù)庫排序。4. 連接方式怎么選OleDb、ODBC 與 Python 的 pyodbc4.1 三種連接方式的適用場景Access 不是網(wǎng)絡(luò)數(shù)據(jù)庫它沒有服務(wù)端監(jiān)聽端口所有連接都是文件級的。常見的連接方式有三種OleDb.NET 原生性能最好、ODBC跨語言Python/Java 都能用、DAO老技術(shù)不推薦新項目用。OleDb 在 Windows 上依賴 ACE 驅(qū)動32 位和 64 位不通用這是最大的坑。如果你的 C# 項目是 64 位但裝的 Office 是 32 位ACE 驅(qū)動可能只有 32 位版本運行時報未注冊提供程序。ODBC 的好處是驅(qū)動獨立可以單獨裝 64 位 Access ODBC 驅(qū)動不依賴 Office。Python 里用pyodbc連 Access連接字符串寫DRIVER{Microsoft Access Driver (*.mdb, *.accdb)};DBQ路徑。缺點是 ODBC 驅(qū)動版本更新慢某些新特性支持不如 OleDb。4.2 Python 操作 Access 的完整示例下面這段 Python 代碼演示用pyodbc做增刪改查包含連接、參數(shù)化查詢和事務(wù)。import pyodbc from datetime import date # 連接字符串DBQ 后面是 accdb 文件的絕對路徑 conn_str ( rDRIVER{Microsoft Access Driver (*.mdb, *.accdb)}; rDBQD:\data\hr.accdb; ) conn pyodbc.connect(conn_str, autocommitFalse) cursor conn.cursor() # 插入?yún)?shù)用 ? 占位順序?qū)?yīng) cursor.execute( INSERT INTO 員工信息 (emp_id, emp_name, dept_code, salary, hire_date) VALUES (?, ?, ?, ?, ?), (E3001, 李四, D02, 9800.00, date(2024, 3, 1)) ) # 查詢fetchall 返回列表每行是 pyodbc.Row cursor.execute(SELECT emp_id, emp_name, salary FROM 員工信息 WHERE dept_code ?, (D02,)) for row in cursor.fetchall(): print(row.emp_id, row.emp_name, row.salary) # 更新 cursor.execute(UPDATE 員工信息 SET salary ? WHERE emp_id ?, (10500.00, E3001)) # 提交事務(wù) conn.commit() cursor.close() conn.close()autocommitFalse是默認(rèn)值意味著必須顯式commit()否則數(shù)據(jù)不落盤。pyodbc的參數(shù)占位符是?和 OleDb 一樣按順序匹配。日期傳datetime.date對象pyodbc會自動轉(zhuǎn)換。如果查詢中文出現(xiàn)亂碼檢查連接字符串里是否加了CHARSETUTF8不過 Access ODBC 驅(qū)動對 UTF-8 支持有限更穩(wěn)的做法是確保系統(tǒng)區(qū)域設(shè)置和文件編碼一致。4.3 連接池與并發(fā)Access 的真實邊界Access 單文件同時只能有一個寫連接多個進(jìn)程同時寫會鎖文件報數(shù)據(jù)庫已被其他用戶鎖定。讀操作可以并發(fā)但寫操作必須串行。如果你的場景是多用戶同時寫Access 不是正確選擇應(yīng)該換 SQLiteWAL 模式或真正的服務(wù)端數(shù)據(jù)庫。單機工具、報表生成、數(shù)據(jù)導(dǎo)入導(dǎo)出這類場景Access 完全夠用。連接池在 Access 上意義不大因為文件級連接開銷本來就低。我一般建議每次操作開一個短連接用完就關(guān)避免長連接持有文件鎖。如果確實要復(fù)用用using或try/finally確保釋放。5. 避坑與排查Access 操作中最容易翻車的五個點5.1 報錯未注冊提供程序 Microsoft.ACE.OLEDB.12.0現(xiàn)象C# 程序在開發(fā)機跑得好好的部署到另一臺機器就報這個錯。原因是目標(biāo)機器沒裝 ACE 驅(qū)動或者裝的位數(shù)和程序不匹配。解決裝對應(yīng)位數(shù)的 Access Database Engine32 位程序裝 32 位驅(qū)動64 位程序裝 64 位驅(qū)動。如果機器上已有 Office注意 Office 位數(shù)會決定默認(rèn)驅(qū)動位數(shù)必要時用/quiet參數(shù)單獨裝驅(qū)動。5.2 中文亂碼或問號現(xiàn)象插入的中文變成???或亂碼。原因通常是連接字符串沒指定編碼或者字段類型用了TEXT但長度不夠?qū)е陆財?。解決OleDb 連接字符串加Jet OLEDB:Global Partial Bulk Ops2意義不大關(guān)鍵是字段用TEXT且長度給夠Python 端確保字符串是str不是bytes。如果從 CSV 導(dǎo)入CSV 要存成 UTF-8 帶 BOM 或 GBK和系統(tǒng)區(qū)域一致。5.3 日期格式報錯標(biāo)準(zhǔn)表達(dá)式中數(shù)據(jù)類型不匹配現(xiàn)象WHERE hire_date 2024-01-01報錯。原因是 Access 的日期字面量必須用#包裹寫成#2024-01-01#。解決參數(shù)化查詢傳DateTime對象不要拼字符串。如果非要拼用#yyyy-MM-dd#格式且月份日期補零。5.4 批量插入后數(shù)據(jù)庫體積暴漲現(xiàn)象插入十萬條數(shù)據(jù)后.accdb文件從幾 MB 漲到幾百 MB刪除數(shù)據(jù)后文件不縮小。原因是 Access 不會自動回收空間刪除只是標(biāo)記。解決用壓縮和修復(fù)數(shù)據(jù)庫功能或者在代碼里調(diào)用DBEngine.CompactDatabase。命令行可以用msaccess.exe /compact。定期壓縮是維護(hù) Access 的必備習(xí)慣。5.5 多線程寫入導(dǎo)致文件鎖死現(xiàn)象兩個線程同時寫報無法更新數(shù)據(jù)庫或?qū)ο鬄橹蛔x或文件已被鎖定。原因是 Access 不支持多寫并發(fā)。解決寫操作加鎖串行化或者改用 SQLite。如果必須用 Access把寫操作集中到一個線程用隊列排隊。讀操作可以多線程但也要注意OleDbConnection不是線程安全的每個線程獨立連接。6. 進(jìn)階技巧用 Access 做數(shù)據(jù)同步和自動化導(dǎo)出Access 最實用的進(jìn)階場景是當(dāng)數(shù)據(jù)中轉(zhuǎn)站從其他系統(tǒng)導(dǎo)出 CSV用 Access 做清洗和關(guān)聯(lián)再導(dǎo)出給報表工具。這里的關(guān)鍵技巧是用鏈接表Linked Table把外部數(shù)據(jù)源掛進(jìn) Access然后用本地查詢做關(guān)聯(lián)避免全量導(dǎo)入。鏈接表的 SQL 寫法是SELECT * FROM 表名 IN 路徑但更穩(wěn)的方式是在界面里建鏈接表再用 SQL 操作。另一個技巧是用 Access 的宏或 VBA 做定時導(dǎo)出。比如每天凌晨把查詢結(jié)果導(dǎo)出成 Excel用DoCmd.TransferSpreadsheet一行代碼搞定。如果不想用 VBA可以用 Python 的pyodbc讀數(shù)據(jù)再用openpyxl寫 Excel靈活性更高。import pyodbc from openpyxl import Workbook conn pyodbc.connect(rDRIVER{Microsoft Access Driver (*.mdb, *.accdb)};DBQD:\data\hr.accdb;) cursor conn.cursor() cursor.execute(SELECT emp_id, emp_name, dept_code, salary FROM 員工信息 WHERE is_active TRUE) wb Workbook() ws wb.active ws.append([工號, 姓名, 部門, 薪資]) for row in cursor.fetchall(): ws.append([row.emp_id, row.emp_name, row.dept_code, float(row.salary)]) wb.save(rD:\data\員工報表.xlsx) conn.close()這段代碼把 Access 查詢結(jié)果直接寫成 Excelfloat(row.salary)是因為CURRENCY類型在 pyodbc 里返回Decimalopenpyxl 不認(rèn)要轉(zhuǎn)成 float。如果數(shù)據(jù)量大用write_onlyTrue模式寫 Excel內(nèi)存占用更低。驗證同步是否成功我一般會做三件事一是對比源表和目標(biāo)表的行數(shù)二是抽樣比對關(guān)鍵字段的哈希值三是跑一遍全量查詢看有沒有報錯。Access 沒有內(nèi)置的校驗和函數(shù)可以用SELECT COUNT(*), SUM(salary)做粗略校驗精確校驗要在應(yīng)用層做。最后說個血淚經(jīng)驗Access 的.accdb文件不要放在網(wǎng)絡(luò)共享盤上直接操作延遲高且容易鎖死。正確做法是復(fù)制到本地操作處理完再傳回去。如果多人協(xié)作用 OneDrive 或共享盤同步文件但同一時間只能一個人寫。這個邊界認(rèn)清之后Access 在單機數(shù)據(jù)管理上的效率比搭一套 MySQL 再寫 ORM 快得多。希望幫到你。本文還有配套的精品資源點擊獲取