Home

You are keen to know which implementation is faster: DLookup or opening a Recordset ??

Just try it and compare the performance with this litte AddIn.

Create a Class e.g. named "clsLookup_Bench":

Option Compare Database
Option Explicit

Public Sub Bench_DLookup()
 Debug.Assert DLookup("Feld1", "tblTest", "ID=1") = "Peter Gugro"
End Sub

Public Sub Bench_DAO_Recordset()
 With CurrentDb.OpenRecordset("SELECT TOP 1 Feld1 FROM tblTest WHERE ID=1")
   Debug.Assert .Fields("Feld1") = "Peter Gugro"
 End With
End Sub

Public Sub Bench_ADO_Recordset()
 With CurrentProject.Connection.Execute("SELECT TOP 1 Feld1 FROM tblTest WHERE ID=1")
   Debug.Assert .Fields("Feld1") = "Peter Gugro"
 End With
End Sub

Then start the VBA Benchmark Add-In:

Information

This Add-In uses the following VBA techniques:

• Class Factory
• Dynamic Code generation ( needs VBE6.DLL VBE6EXT.OLB )
• Dynamic Function Calls using TLI ( needs TLBINF32.DLL )
• Access Unit Tests ( see http://accunit.access-codelib.net/ )

Installation

• Download the Benchmark Add-In
• In MS Access go to the Add-In Manger, click "Add..." and select the Benchmark.mda from your Download Folder

Usage

• Open the VBA Editor with Alt-F11
• Create a new Class Module and save it with a name ending on "_Bench"
• Create a "Public Sub" for each test case you would like to compare
• In the Access Add-In menue ( not the VBA Add-In menue ! ) you will find the entry "RunBenchmarks"