<

Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

Creating a Faster DLookup()

Published on
14,153 Points
4,053 Views
6 Endorsements
Last Modified:
Awarded

Introduction:

Have you ever been given the advice NOT to use domain functions inside queries?  I have, and I have repeated that advice over and over again; why?  The main reason is that this process is inherently slow.

But what if you absolutely need that value in your dataset?  I recently ran into this problem, and no matter what I tried, I could not get a particular field into a dataset and keep the rest of the recordset updateable, without using a DLookup().  This was extremely frustrating for users of the application, because it took close to 30 seconds for the datasheet to finish "calculating" every time the form was loaded or refreshed (this with only about 450 records). 

There are two primary reasons for the slowness associated with including domain functions in queries:
1.  Every call to a domain function requires that a new recordset be created using the table you want to grab data from and using the critieria you provided.  This is the same thing that happens when you use a correlated subquery (sample below) in your queries.
SELECT A.ID, A.Description,
(SELECT B.SomeValue FROM B WHERE B.SomeID = A.ID) as SomeValue
FROM A

Open in new window

2.  When you use a domain function (DLookup, DMax, DMin, DSum) or a user defined function in your query, the query parser is unable to see inside the function, so it has no means of determining the optimal execution plan.  This is the main reason you don't want to use Domain functions (or UDF's that return data) in queries.

In my case, the query I was using was selecting data from a single table with a criteria on a date field, so I was not too concerned about the effect of #2 (above) and felt I needed to focus on #1.  I did some research and found that another developer (Allen Browne) had developed a version of the DLookup() function, called ELookup(), which is quicker, and provides additional functionality, but still opened a recordset every time it was called, and required that I build a criteria string within the context of my query.  I was not happy with either of those requirements, so I asked a couple of other Access experts for recommendations.  After some discussion, I combined several of their recommendations into a solution which was significantly faster (under 2 seconds).

Creating a User Defined Function (UDF):
To be clear, this solution involves writing your own user defined function, but the speed with which this operates makes it well worth it.  The first step in developing this lookup function was to determine the parameters which would be required to find the value I was looking for, and determine the datatype I wanted to return to the calling process.  In this particular instance, I was looking for a Tax Rate, which was determined by the value of three fields ([State_CD], [Product], [Rate_CD]) in my query (qry_Current_Tax_Rates).  I realized that I would need to pass these three values into the function as arguments and, because I could not guarantee that all of these values would be present in the underlying recordset, I had to declare them as variants.  I also determined that if any of those values was missing, or I could not find a value in my recordset consistent with those three values, I would return a NULL to the calling process, so I declared the return value of the function as a variant as well.
Public Function fnTaxRateLookup(StateCode as Variant, _
                                ProductCode as Variant, _
                                RateCode as Variant, _
                                Optional Reset as Boolean = False, _
                                Optional CloseRS as Boolean) as Variant

Open in new window


Defining function variables:
When I started work on this function, I declared a global recordset variable that I could access from anywhere in my application, but as I used the function within my application, I decided that what I really needed was a static variable that would retain its value between function calls.  I also realized that because my source data had a limited number of States, Products, and Rate Codes, that I could speed up the application by not performing the lookup operation if the values passed to the function  were the same as the previously passed values, so my variable declarations morphed into:
Static myRS As DAO.Recordset
Static myState As Variant
Static myProduct As Variant
Static myRateCode As Variant
Static myRate As Variant
          
Dim strSQL As String
Dim strCriteria As String

Open in new window


Checking for repeat arguments:


As I implemented this function I found that the source recordset (where the functions arguments came from) was frequently sorted by the fields used in this function, I determined I could speed the process up by not performing the lookup if the arguments for the current funcion call were identical to those passed during the previous call.  The code for that segment of the function looks like:
fnTaxRateLookup = Null
If IsNull(StateCode) Or IsNull(ProductCode) Or IsNull(RateCode) Then Exit Function
          
If (Nz(myState, "") = StateCode) And (Nz(myProduct, "") = ProductCode) _
And (Nz(myRateCode, "") = RateCode) Then
    fnTaxRateLookup = myRate
    Exit Function
End If

Open in new window


Resolving the recordset issue:

As mentioned in the introduction, part of the reason that using the DLookup() function in this situation is so slow is that it has to open a new recordset based on the parameters you provide, every time you call the function.  To avoid this, I created a static recordset and check to see whether that recordset has been instantiated each time the function is called.

If (myRS Is Nothing) Or Reset Then
     Set myRS = CurrentDb.OpenRecordset("qry_Current_Tax_Rates", , dbFailOnError)
End If

Open in new window


Retrieving the lookup value:

There are two generally accepted methods for finding a record in a DAO recordset which meet a specified criteria.  The recordset.FindFirst method allows you create a criteria string to search for within the recordset.  The syntax I used in my function was:

Dim strQuote as string

strQuote = chr$(34)
strCriteria = "[State_CD] = " & strQuote & StateCode & strQuote _
        & " AND [Product] = " & strQuote & ProductCode & strQuote _
        & " AND [Rate_CD] = " & strQuote & RateCode & strQuote
With myRS
    .FindFirst strCriteria
    IF .NoMatch Then
        fnTaxRateLookup = NULL
    ELSE
        fnTaxRateLookup = !TaxRate
    END IF
End With

Open in new window


The recordset.SEEK method is significantly faster than the FindFirst method because it takes advantage of an index.  However, this method requires that:

  1. the recordset be opened as a table data type (dbOpenTable)
  2. the fields being searched are contained within a named index
Although you cannot use this method with a linked table, you can set a reference to that table in its source database, as opposed to referencing the linked table in the currentdb.  To do so, the syntax used to set the reference to the recordset would be:
If (myRS Is Nothing) Or Reset Then
     Dim db as DAO.Database
     Set db = DBEngine.OpenDatabase("SourceDatabaseName")
     Set myRS = Db.OpenRecordset("SourceTableName", dbOpenTable, dbFailOnError)
End If

Open in new window


The query I was using in my function did not meet these requirements, but if it had, the syntax for using the SEEK method would have been:

With myRS
    .Index = "StateProductRate"   '<= this is the name of the index
    .Seek "=", StateCode, ProductCode, RateCode
    IF .NoMatch then 
        fnTaxRateLookup = NULL
    ELSE
        fnTaxRateLookup = !Tax_Rate
    END IF
END With

Open in new window


Note:  For more information on fast searchs visit Marcus Fischer's article on Fast Table Lookup Functions.

As I implemented this solution, I realized that I also needed a way to close the recordset, since it was not being closed at the end of each call.  To do so, I added an additional, optional argument to the function declaration and added a couple of additional lines of code to the function:
If CloseRS Then
    If (myRS Is Nothing) = False Then
        myRS.Close
        Set myRS = Nothing
    End If
    If Reset = False Then Exit Function
End If

Open in new window

In the Close event of the form that uses this function, I simply call the function and pass it NULL values for all the arguments but CloseRS.

fnTaxRateLookup NULL, NULL, NULL,, True

Open in new window


The final code for my "Faster Dlookup" function is shown below.  So far, I've found that this works well in both queries as well as in the Control Source property of  textbox on a form.
Public Function fnTaxRateLookup(StateCode As Variant, _
                          ProductCode As Variant, _
                          RateCode As Variant, _
                          Optional Reset As Boolean = False, _
                          Optional CloseRS As Boolean = False) As Variant

     Static myRS As DAO.Recordset
     Static myState As Variant
     Static myProduct As Variant
     Static myRateCode As Variant
     Static myRate As Variant
         
     Dim strSQL As String
     Dim strCriteria As String
          
     On Error GoTo ProcError

     'Explicitly set the default return value of the function
     fnTaxRateLookup = Null
          
     'If user passed the CloseRS = True, then close the recordset
     If CloseRS Then
         If (myRS Is Nothing) = False Then
             myRS.Close
             Set myRS = Nothing
         End If
         If Reset = False Then Exit Function
     End If
     
     'If any of the required arguments is NULL, exit function     
     If IsNull(StateCode) Or IsNull(ProductCode) _
     Or IsNull(RateCode) Then Exit Function

     'If all of the required arguments are the same as during the 
     'previous function call return the previous selected rate
     If (Nz(myState, "") = StateCode) _
     And (Nz(myProduct, "") = ProductCode) _
     And (Nz(myRateCode, "") = RateCode) Then
         fnTaxRateLookup = myRate
         Exit Function
     End If
          
     'Open the recordset if not already open or Reset = true
     If (myRS Is Nothing) Or Reset Then
         Set myRS = CurrentDb.OpenRecordset("qry_Current_Tax_Rates", , dbFailOnError)
     End If
          
     strCriteria = "[State_CD] = " & Quotes(StateCode) & " AND " _
                 & "[Product] = " & Quotes(ProductCode) & " AND " _
                 & "[Tax_Rate_Code] = " & Quotes(RateCode)
     With myRS
         .FindFirst strCriteria
         myRate = IIf(.NoMatch, Null, !Tax_Rate)
     End With

     'set the value of the static variables
     myState = StateCode
     myProduct = ProductCode
     myRateCode = RateCode
     fnTaxRateLookup = myRate
          
ProcExit:
     Exit Function

ProcError:
     Debug.Print "fnTaxRateLookup", Err.Number, Err.Description
     MsgBox Err.Number & vbCrLf & Err.Description, , "fnTaxRateLookup"
              
End Function

Open in new window

 
6
Comment
Author:Dale Fye
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 48

Author Comment

by:Dale Fye
@EricPete

I pulled them all out in the latest version.

What I was trying to do was get a label just below the code blocks, centered, but because of the "Select All" and "Open in new Window" hyperlinks below each block, the labels were well below the image.

I guess I could add the text to the the bottom of the code block, but I checked and did not see any references to the code blocks any more, so don't think I need to worry about it.

Thanks.
0
 
LVL 66

Expert Comment

by:Jim Horn
Nice job.  Voting Yes.
0
 
 

Administrative Comment

by:Eric AKA Netminder
Dale,

The Page Editors have determined that this article is worthy of Awarded status.

Congratulations!

ericpete
Page Editor
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Join & Write a Comment

Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month