vba/VB6 ADO 不同驱动连接MDB数据库用时测试

用时---10.7807毫秒 - Jet.OLEDB 用时---18.7443毫秒 - ACE.OLEDB.12.0 用时---25.3630毫秒 - ProviderMSDASQL 用时---27.2455毫秒 - Driver_ODBC 用时---27.9928毫秒 - Driver_ODBC(含Accdb) --------------------------- ADO方式连接用时要更快速度加上只读模式所有驱动通用ReadMode IIf(isRead, ;ModeRead, )实测速度排名从快 → 慢Jet.OLEDB —— 10.78ms绝对第一最快ACE.OLEDB.12.0 —— 18.74ms第二accdb 专用ProviderMSDASQL —— 25.36ms第三Driver_ODBC —— 27.25ms第四Driver_ODBC (含 Accdb) —— 27.99ms最慢Public Conn As ADODB.Connection, ConnOpenn As Boolean Public HidErr As Boolean, DbErr As String Function OpenAccess_Driver(DbFile As String, Optional DriverID As Long, Optional ByVal isRead As Boolean, Optional Mdbpassword As String) As Boolean On Error GoTo ERR Dim ConnStr As String Dim ReadMode As String ErrInfo Set Conn New ADODB.Connection Conn.ConnectionTimeout 3000 只读模式所有驱动通用 ReadMode IIf(isRead, ;ModeRead, ) Select Case DriverID 0-Jet.OLEDB 1-Driver_ODBC 2-ProviderMSDASQL 3-Driver_ODBC(Accdb) 4-ACE.OLEDB.12.0 Case 0 最快Jet OLEDB 4.0 ConnStr ProviderMicrosoft.Jet.OLEDB.4.0;Data Source DbFile _ ;Persist Security InfoFalse _ IIf(Mdbpassword , ;Jet OLEDB:Database Password Mdbpassword, ) _ ReadMode Case 1 ODBC Driver (mdb only) ConnStr Driver{Microsoft Access Driver (*.mdb)};DBQ DbFile _ IIf(Mdbpassword , ;PWD Mdbpassword, ) _ ReadMode Case 2 MSDASQL ODBC ConnStr ProviderMSDASQL.1;Extended PropertiesDRIVERMicrosoft Access Driver (*.mdb);DBQ DbFile _ IIf(Mdbpassword , ;PWD Mdbpassword, ) _ ReadMode Case 3 ODBC Driver (mdb accdb) ConnStr Driver{Microsoft Access Driver (*.mdb, *.accdb)};DBQ DbFile _ IIf(Mdbpassword , ;PWD Mdbpassword, ) _ ReadMode Case 4 ACE OLEDB 12.0 (accdb专用) ConnStr ProviderMicrosoft.ACE.OLEDB.12.0;Data Source DbFile _ ;Persist Security InfoFalse _ IIf(Mdbpassword , ;Jet OLEDB:Database Password Mdbpassword, ) _ ReadMode End Select Conn.Open ConnStr OpenAccess_Driver True ConnOpenn True Exit Function ERR: DbErr OpenAccess 错误号 ERR.Number ,信息 ERR.Description If Not HidErr Then MsgBox DbErr Debug.Print DbErr OpenAccess_Driver False ConnOpenn False End Function Function OpenAccess_Driver(DbFile As String, Optional DriverID As Long, Optional Mdbpassword As String) As Boolean On Error GoTo ERR Dim ConnStr As String ErrInfo Set Conn New ADODB.Connection Conn.ConnectionTimeout 3000 Select Case DriverID Jet.OLEDB-Driver_ODBC-ProviderMSDASQL-Driver_ODBC(含Accdb)-ACE.OLEDB.12.0; Case 0 最快连接方式 ConnStr ProviderMicrosoft.Jet.OLEDB.4.0;Persist Security InfoFalse;Data Source DbFile Case 1 ODBC 方式比 OLEDB 慢而且兼容性差 ConnStr Driver{Microsoft Access Driver (*.mdb)};dbq DbFile Case 2 ConnStr ProviderMSDASQL.1;Persist Security Infofalse;User IDsa;Extended PropertiesDRIVERMicrosoft Access Driver (*.mdb);DBQ DbFile ;Password Mdbpassword Case 3 Accdb ConnStr Driver{Microsoft Access Driver (*.mdb, *.accdb)};Dbq DbFile Case 4 ConnStr ProviderMicrosoft.ACE.OLEDB.12.0;Persist Security InfoFalse;Data Source DbFile ;Jet OLEDB:Database Password123456 只读模式;ModeRead End Select Conn.Open ConnStr OpenAccess_Driver True ConnOpenn True Exit Function ERR: DbErr OpenAccess 错误号 ERR.Number ,信息 ERR.Description If Not HidErr Then MsgBox DbErr Debug.Print DbErr End Function一、最重要结论你自己跑出来的真理✅VB6 .mdb 最快 Jet.OLEDB 4.0比 ACE 快≈ 42%比 ODBC 快≈ 60%比你原来的 MSDASQL 快≈ 57%✅ACE 只适合 accdb不适合 mdb你现在用的是.mdb所以ACE 反而更慢完全正常二、为什么会这样原理一句话Jet.OLEDB 是 mdb 原生引擎ACE 是为 accdb 设计的新引擎兼容 mdb 但速度更慢ODBC / MSDASQL 多一层转换必然最慢三、给你最终最佳选择按你实测结果你用.mdb✅永远用Jet.OLEDB 4.0vbProviderMicrosoft.Jet.OLEDB.4.0;Data Sourcexxx.mdb最快、最稳、最兼容 Win7 32 位你用.accdb✅只能用ACE.OLEDB.12.0vbProviderMicrosoft.ACE.OLEDB.12.0;Data Sourcexxx.accdb四、你原来的代码最慢可以彻底扔掉了vbProviderMSDASQL.1;.... DRIVER...你实测25.36ms比最快的Jet 10.78ms 慢了 135%五、终极一句话你记住这个就够.mdb 最快 Jet.OLEDB.accdb 最快 ACE.OLEDBODBC / MSDASQL 一律最慢不要用