Visual Basic 6 / VBA 存取 Access 資料庫歷經 DAO (Data Access Objects) 與 ADO (ActiveX Data Objects) 兩代物件模型。本文詳細比較兩種寫法、適用版本、優缺點與遷移建議。
Dim MyWs As Workspace
Dim MyDB As Database
Dim wordTab As Recordset
Set MyWs = DBEngine.CreateWorkspace("", "Admin", "", dbUseJet)
Set MyDB = MyWs.OpenDatabase("資料庫路徑.mdb", False, False)
Set wordTab = MyDB.OpenRecordset("SELECT * FROM 表名", dbOpenDynaset)
Dim LocCnn As ADODB.Connection
Dim LocRst As ADODB.Recordset
Set LocCnn = New ADODB.Connection
LocCnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "\資料庫.mdb;" & _
"Jet OLEDB:Database Password=your_pwd;"
Set LocRst = New ADODB.Recordset
LocRst.CursorType = adOpenKeyset
LocRst.LockType = adLockOptimistic
LocRst.Open "SELECT * FROM 表名", LocCnn, , , adCmdText
BeginTrans/CommitTrans 批次處理提升效能。adUseClient) 大資料量佔記憶體。| Access 版本 | 檔案格式 | DAO (Jet) | ADO (OLE DB Provider) |
|---|---|---|---|
| 97 | .mdb (Jet 3.5) | ✅ 原生 | ✅ Jet 3.5 Provider |
| 2000/2002/2003 | .mdb (Jet 4.0) | ❌ 需 DAO 3.6 | ✅ Jet 4.0 Provider |
| 2007/2010+ | .accdb (ACE) | ✅ DAO 3.6/ACE | ✅ ACE.OLEDB.12.0+ |
CurrentDb (DAO) 最方便,無需建立連線。BeginTrans/CommitTrans) 比 DAO Workspace.BeginTrans 更穩定。掌握兩套模型差異,能在專案維護與技術升級中做出正確選擇。