My larger point was DAO will work immensely faster with Access databases
than ADO will. Try any test you want, and DAO will almost always be faster.
Perhaps I do not understand myself what the word means 'immensely'?
Here any test I want:
' ### Code start here ###
Option Explicit
Private Declare Function GetTickCount _
Lib "Kernel32" () As Long
Private Const CONN_STRING = _
"C:\db1.mdb"
Private Const SQL = _
"select ID from MillionRecords order by ID"
Sub main()
Dim i As Long
For i = 1 To 6
Debug.Print TestADO
Debug.Print TestDAO
Next
End Sub
Function TestADO() As String
Dim lTick As Long
Dim rs As ADOR.Recordset
Dim lValue As Long
Set rs = New ADOR.Recordset
With rs
.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data source=" & CONN_STRING
.Source = SQL
.CursorType = adOpenStatic
.LockType = adLockReadOnly
lTick = GetTickCount
.Open
.MoveLast
lValue = .Fields(0).Value
TestADO = "AD0=" & GetTickCount - lTick
.Close
End With
End Function
Function TestDAO() As String
Dim lTick As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lValue As Long
Set db = DBEngine.Workspaces(0).OpenDatabase( _
CONN_STRING, , True)
lTick = GetTickCount
Set rs = db.OpenRecordset(SQL, dbOpenSnapshot)
With rs
.MoveLast
lValue = .Fields(0).Value
TestDAO = "DAO=" & GetTickCount - lTick
.Close
End With
Set rs = Nothing
db.Close
Set db = Nothing
End Function
' ### Code end here ###
For me, the results:
AD0=6719
DAO=6579
AD0=6734
DAO=6485
AD0=6750
DAO=6156
AD0=7063
DAO=7125
AD0=6578
DAO=7062
AD0=6985
DAO=6062
For me, the results are not to be extremely different. And you?
