title: Access increase speed of DLookup by "caching" tables or other strategy?
body: <h2>Background</h2>
  <p>I have an Access splitform with <a href="https://stackoverflow.com/q/21837941/1048539">multiple DLookups</a>. 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.</p>
  <p>The Access frontend is linked to SQL tables.</p>
  <p>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.</p>
  <h2>Research</h2>
  <p>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.</p>
  <pre><code>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 = &quot;Select FullName from ToolDesigners Where ToolDesignersID=4;&quot;
      
      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(&quot;s&quot;, startTime, Now)
      Debug.Print &quot;SQL Total time:&quot; &amp; vbTab &amp; DateDiff(&quot;s&quot;, startTime, Now)
      Debug.Print &quot;SQL Average time:&quot; &amp; vbTab &amp; mDiff / numbTries
      
      '
      '
      '
      '
      '
     
      
      
      startTime = Now
      
      For count = 1 To numbTries
          t = DLookup(&quot;FullName&quot;, &quot;ToolDesigners&quot;, &quot;ToolDesignersID=4&quot;)
      Next count
      
      
      mDiff = DateDiff(&quot;s&quot;, startTime, Now)
      Debug.Print &quot;DLookupUp Total time:&quot; &amp; vbTab &amp; DateDiff(&quot;s&quot;, startTime, Now)
      Debug.Print &quot;DLookupUp Average time:&quot; &amp; vbTab &amp; mDiff / numbTries
      
  end sub
  </code></pre>
  <p>(I understand this is only precise to single seconds)</p>
  <p>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.</p>
  <p>This causes very slow form refresh as well as VERY slow datasheet refresh.</p>
  <p>I then tested against a SQLExpress database hosted on my machine - times dropped to 0.0005 seconds on average.</p>
  <h2>Question</h2>
  <p>It seems DLookups are slow in this application. I am hoping to find an alternative and faster approach.</p>
  <p>What I would <em>like</em> 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?</p>
  <p>It seems if I was referring to another Access database I could just use &quot;opendatabase&quot; 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.</p>
  <p>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.</p>