Access increase speed of DLookup by "caching" tables or other strategy?

Question

Background

I have an Access splitform with multiple DLookups. There are about 10 total DLookups on the form and there are approximately 25-50 records displayed at any one time in the Splitform view.

The Access frontend is linked to SQL tables.

When the DLookup values are displayed in the Datasheet view, it becomes quite slow to view the information, because there are frequent recalculations (each time anything in the dataset changes Access appears to recalculate all DLookups for the entire Splitform datasheet). This was very noticeably and unacceptably slow when connecting through VPN.

Research

I decided to investigate and wrote the following to determine why things were so slow. I also wanted to check if DLookup was slower than a SQL query for some reason.

sub testLotsofDlookups()
    
    Dim count As Integer
    Dim startTime As Date
    Dim endTime As Date
    Dim numbTries As Integer
    Dim t As String
    
    numbTries = 100
    startTime = Now
    count = 0
    
    Dim dbs As DAO.database
    Dim rsSQL As DAO.Recordset
    Dim strSQL As String
    
    Set dbs = CurrentDb
    
    'Open a snapshot-type Recordset based on an SQL statement
    strSQL = "Select FullName from ToolDesigners Where ToolDesignersID=4;"
    
    startTime = Now
    For count = 1 To numbTries
        Set rsSQL = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
        t = rsSQL.Fields(0)
    Next count
    Dim mDiff As Double
    mDiff = DateDiff("s", startTime, Now)
    Debug.Print "SQL Total time:" & vbTab & DateDiff("s", startTime, Now)
    Debug.Print "SQL Average time:" & vbTab & mDiff / numbTries
    
    '
    '
    '
    '
    '
   
    
    
    startTime = Now
    
    For count = 1 To numbTries
        t = DLookup("FullName", "ToolDesigners", "ToolDesignersID=4")
    Next count
    
    
    mDiff = DateDiff("s", startTime, Now)
    Debug.Print "DLookupUp Total time:" & vbTab & DateDiff("s", startTime, Now)
    Debug.Print "DLookupUp Average time:" & vbTab & mDiff / numbTries
    
end sub

(I understand this is only precise to single seconds)

Interestingly, I found that on average each DLookup and SQL query was taking nearly 0.5 seconds. While working on company intranet, I still have times of over 0.10 seconds on average. Both are very comparable in speed.

This causes very slow form refresh as well as VERY slow datasheet refresh.

I then tested against a SQLExpress database hosted on my machine - times dropped to 0.0005 seconds on average.

Question

It seems DLookups are slow in this application. I am hoping to find an alternative and faster approach.

What I would like to be able to do is to somehow cause the DLookup to run against local tables Access presumably keeps rather than the SQL tables on the server. It seems I could either create temp tables every time I open a form or the database (not a fan) - is there a better way?

It seems if I was referring to another Access database I could just use "opendatabase" which then keeps it in memory. This then increases the speed of queries against that database. 100% of the examples I find are referring to Access databases though, not SQL.

Alternatively I could use something other than DLookup, which is what I thought when testing the SQL commands but I'm not really sure what to do because SQL was comparable speed.

Answer 1

If it's just single values then I'd be inclined to use a simple in-memory cache -

Private mToolDesignerFullNameCache As New Scripting.Dictionary

Function GetToolDesignerFullName(Criteria As String)
  If mToolDesignerFullNameCache.Exists(Criteria) Then
    GetToolDesignerFullName = mToolDesignerFullNameCache(Criteria)
  Else
    Dim Name
    Name = DLookup("FullName", "ToolDesigners", Criteria)
    mToolDesignerFullNameCache.Add(Criteria, Name)
    GetToolDesignerFullName = Name
  End If
End Function

Sub ResetToolDesignerFullNameCache()
  mToolDesignerFullNameCache.RemoveAll
End Sub

Requires adding 'Microsoft Scripting Runtime' as a VBA reference to compile. In the past I have found this sort of thing useful even when using an Access backend given how often the Access UI will poll for data.