Does access increase DLookup speed by caching tables or another strategy?

Question

Background

I have a shared access form with several DLookups . There are about 10 generic DLookups in the form, and there are about 25-50 entries in the Splitform view.

The access interface is associated with SQL tables.

When DLookup values ​​are displayed in the Datasheet view, it becomes rather slow to view information because recalculations often occur (every time something in a dataset changes, Access seems to recalculate all DLookups for the entire Splitform data table). This was very noticeable and unacceptably slow when connecting via VPN.

Study

I decided to research and write the following to determine why everything is so slow. I also wanted to check if DLookup was slower than the 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 that this is only accurate to one second)

Interestingly, I found that on average each DLookup and SQL query took almost 0.5 seconds. While working on the corporate intranet, I still have an average time of more than 0.10 seconds. Both of them are very comparable in speed.

This causes a very slow form update, as well as a VERY slow data update.

Then I tested the SQLExpress database hosted on my machine - at times it dropped to 0.0005 seconds on average.

Question

DLookups seem to be slow in this application. I hope to find an alternative and quick approach.

, DLookup . , SQL- . , , ( ) - ?

, Access, "opendatabase", . . 100% , , Access, SQL.

- , DLookup, SQL, , , SQL .

Answer 1

, -

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

"Microsoft Scripting Runtime" VBA . Access, , .