In my tests the two functions perform at the same speed.
Code:
Public Sub test1()
cTest.StartP
Debug.Print DLookup("salesorderid", "tblSalesTable", "LineNumber=12") & Space$(1) & cTest.EndP
End Sub
Public Sub test2()
Dim rs
cTest.StartP
Set rs = CurrentDb.OpenRecordset("select salesorderid from tblSalesTable where linenumber=12")
Debug.Print rs!SalesOrderID & Space$(1) & cTest.EndP
Set rs = Nothing
End Sub
my profile class
Code:
Option Compare Database
' This Class is used to clock and optimize response times at the debug level in the database.
Private Declare Function GetTickCount Lib "kernel32" () As Long
Private Start As Long
Private Finish As Long
Public Sub StartP()
Start = GetTickCount
End Sub
Public Function EndP() As Double
Finish = GetTickCount
EndP = (Finish - Start) / 1000
End Function
Changing the 2nd function to Pull * instead of 1 field, is still less 0.1 seconds from 100k records on a database that is stored on a network share.