Faster DLookup
By Richard Rost
10 months ago
Speed Up Code & Forms with This Simple Technique In this Microsoft Access tutorial I will show you how to speed up your record lookups by using a method faster than DLookup, especially in performance-critical situations like OnCurrent events and looping code. We will compare DLookup with direct SQL queries and recordset techniques, test performance against large tables, and discuss when to use each approach for optimal results. This is for advanced users familiar with DLookup, NZ, VBA, recordsets, and basic SQL. MembersThere is no extended cut, but here is the file download: Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
PrerequisitesLinksRecommended CoursesUp Next
Keywords TechHelp Access, DLookup, recordset, SQL query, OnCurrent event, performance optimization, VBA, Timer function, DCount, NZ function, StatusBox, MessageArchiveT, dbOpenSnapshot, TOP 1, code optimization, SELECT statement, primary key, lookup speed, null checking, customer form
Subscribe to Faster DLookup
Get notifications when this page is updated
Intro In this video, we'll talk about finding a faster way to look up records in Microsoft Access compared to using DLookup. We'll cover why DLookup and DCount can slow down your database when checking if records exist, and when it's better to use other methods. I'll show you how to use DLookup with NZ to check for existing records, demonstrate how to measure the performance with the Timer function, and discuss how repeated lookups can impact the speed of your forms. We'll also walk through building a form button to perform a DLookup and display status messages using StatusBox.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
Got one for the developer students today, and today I'm going to label it extra nerdy. This is for the advanced, advanced nerdy developers. I'm gonna show you how to look up records faster than DLookup. DLookup is great. I love DLookup. I use it all the time, but it's not the fastest function, and if you're using it in a loop or you use it constantly, like when records load in your OnCurrent event, it can be slow.
So today, I'm gonna show you how to use a technique faster than DLookup to look up values. In yesterday's video, I showed you about the Timer function and how you can use it to figure out what parts of your code are causing slowdowns. If you've got a long OnCurrent event in a particular form or lots of code in a button, for example, you can put Timer statements in there so you can see which lines of code are taking the longest - this one's four seconds, this one's two seconds, and so on. Then we can optimize those, and today we're gonna start optimizing stuff.
Also, in one of my previous videos, I talked about a very important tip: if all you care about is whether a record exists or not, don't use DCount. People make this mistake all the time - I used to make this mistake all the time before I knew about this. Checking a DCount is much, much slower than using DLookup. The problem is DCount is gonna actually run down all of the records, and this will be very slow, especially with large tables.
So if you just want to know whether something exists or not, use DLookup instead. I used to do it all the time. I'm like, do I have any emails from this customer? So I would DCount it and look for a zero, but it's got to go out and count all of the emails first just to tell me whether or not they have any emails in the system. So if you just want to know if something exists, use DLookup instead. Try to pull a single field, any field. I like to pull the IDs and check if the result is a null or not with an NZ, and if it returns anything, then a record exists. That's it, you're done - way faster. In some cases, it can be twice as fast or more. So watch that other video if you want to learn about that.
Now, if you've been watching my videos for any length of time, you know I love DLookup. I use it everywhere, along with DCount, DMax, DMin - all the D functions. There's nothing wrong with them. They're great tools, and I will continue to use them and teach them. But like any tool, they have their place. The problem is they're not optimized for speed, and we'll get into the really nerdy reason why later.
Now, the D functions - they're convenient, they're easy to use, and they work fine in most situations, but they're not exactly fast, especially when you call them over and over again in something like an OnCurrent event, which runs every time a record loads. I've got several lookups in my OnCurrent event for my customer form. I look up to see what their membership level is. I look up to see if they have any emails in the system, if they have any unpaid orders. So there's three or four, maybe like six lookups that happen every time I load a customer.
Now, if this is a lookup you do occasionally - a form that opens once in a while or something like a DSum on your main menu to show you the day's sales - something like that, that's not a big deal. Keep using the D functions. They're easy, they're convenient. But if it's a form you use constantly or you're running code over and over again in loops, they could be slowing things down using DLookup.
So it's time to pull out more advanced tools from our toolbox. You got forms you use constantly throughout the day, code that runs every record change like an OnCurrent event, performance-sensitive ideas, loops that you've got - that kind of stuff. Well, what's the solution? I'm gonna talk about the solution briefly, then we're gonna take a look at the code, and then we're gonna go into some nerdiness afterwards. So we're gonna do a little bit of theory, and then the code, and then a lot of theory after.
All right, so the way that you do this is you're gonna use a direct SQL query. It's gonna be the SQL in your code, not a saved query. We're gonna open a recordset in dbOpenSnapshot mode, which is the fastest. We're gonna get just one record using the TOP 1 parameter in our SQL statement. Then we'll quickly check to see if any record exists. It's gonna be one or zero at this point. So if we're at rs.EOF that means there's no record. Fast and efficient, it'll exit out, or we'll get the value. We're only pulling one record out of the table.
Now, I've had this cut my execution time in half. I had a form that was opening, it took eight seconds to open, and then it dropped down to four, which doesn't seem like much, but four seconds every time you open up a form - and if you open that form up a hundred times a day, you do the math. That's like a lot of seconds or something.
Now we're gonna get to the code in a minute, but don't panic. You don't have to rebuild your entire database and purge DLookups from the entire thing. But when you're optimizing performance-critical parts of your application, this is a technique worth knowing. It's another tool for your box of tools, otherwise known as your toolbox. So this is for advanced developers - use it when you need it. You don't have to put it everywhere, although I've started now. Now that I put this in my database, every time I go to use a DLookup, I just use my DLookup function instead, so it's a lot faster. Every millisecond counts when you scale. So if you start adding users, lots of records, things start to slow down.
Well, let's talk about some prerequisites. Obviously, this is advanced VBA in this one. So if you're not there yet, this is a good place to start. Watch my intro to VBA - about 20 minutes long. It'll teach you what you need to know to get started with VBA, and then there's a bunch of other videos you can watch to kind of get up to speed. You should know what variables are, how to declare them, how to use them. Of course, you should be well familiar with DLookup, DCount, DMax, DMin, DSum, all of the D functions. And of course, NZ is like DLookup's little cousin. You're always gonna use NZ to wrap it, checking for nulls. You should know how to use If Then Else blocks.
We're gonna use my StatusBox function to display status on the main menu. It's easier than using MessageBox. I don't like the Immediate window, so I built this little guy - go watch this video. Here's the big one: you should definitely know recordsets. The key to doing this properly is to use a recordset. If you don't know what a recordset is, go watch this first, definitely. And you should know some basic SQL. You don't need to know all the advanced stuff - just some basic SELECT statements will get you by.
Now, all of these are free videos. They're on my YouTube channel, they're on my website, go watch all of them if you're not sure about any of this stuff, and then come on back.
Okay, here I am in my TechHelp free template. This is a free database. You can grab a copy off my website if you want to now. What I have done with this database is I linked it to my MessageArchiveT. It's in a different database. It's sitting on my server. What I do with my emails: all my emails come into my Access database that I use every day for chat with you guys, customer service, that kind of stuff, and I've got all of the emails ever sent to my customer service form on my website, and by regular email going back to 2003.
When they're a year old, they get sent to the MessageArchive table. This table has over 200,000 records in it. It's about 600 megs in size, so I'm gonna probably need to be doing some pruning soon, but this is the perfect table to be testing this stuff with. So I want to check and see if there are any emails to my email address in here.
I'm gonna go to the customer form. Let's slide this over here. I'm just gonna make a button that's gonna check and see if this person has any emails in the MessageArchive table. We're gonna do it the old-fashioned way first, using DLookup. If you use DCount, then that's gonna be really slow. I'm not even gonna bother - the other video, I prove that definitively. So we're just gonna check to see if this guy has emails.
We're gonna copy one of these buttons: copy, paste - all right. Check email. Right click, build event.
Firstly, you'll notice yes, I forgot to name my button. Don't worry about it. This is just temporary. I always name my buttons. Alex will yell at me if I don't, but only if it's a button I plan on keeping around. If it's just temporary like this, I don't care.
We're gonna look up a message ID from that MessageArchive table. We're gonna see if it exists for the current user. I just want to know if any emails exist, so:
Dim messageID As Long
That is the primary key in the MessageArchive table.
Now I'm gonna say:
messageID = NZ(DLookup("MessageID", "MessageArchiveT", "SenderEmail = """" & Email & """"), 0)
I'm looking up a message ID from the MessageArchive table where the email address in that table is SenderEmail = and I'm looking for the email address of the current customer. So it's a string, so it's gonna be double double quotes right, and Email is the field on the form. If you don't know what double double quotes are, go watch this video. Essentially, that's a string value, so you got to put it inside quotes. Two double quotes becomes a single double quote.
And, oh, we forgot our NZ, so ", 0". This says look up this email address in this table and return the message ID. Just find one - that's what the lookup says, I just care about the first one. Well, you can't necessarily guarantee it's the first one, but you're gonna get a record to use.
If it doesn't find one, it'll return a null, which NZ converts to a zero.
Now,
If messageID = 0 Then
Status "No emails found"
Else
Status "Emails were found"
End If
Save it.
Debug, compile once in a while. Close it, and let's check for email. All right, emails were found. They ran relatively quickly.
Let's try somebody else. I don't think there's a Jim Curtain there. Let's click, no email. So that's probably about right.
Now let's see how long this takes. Let's use the trick from last time. We're gonna:
Dim t As Double
All right, up here:
t = Timer
Then down here at the end:
Status "Time elapsed: " & (Timer - t) & " seconds"
All right, let's see how many seconds that took. Ready, go.
Okay, it took 0.25 seconds.
Again, this is just a single lookup, and what we're going for here is, if you got lots of lookups in a form, you've got really complicated code. This is just a simple example, but you get where I'm coming from.
So now we know how to do this lookup and how to do it the old-fashioned way. Next, we're gonna see how to do it the new, fast, optimized way, and we'll see how to do that in tomorrow's video. So, tune in tomorrow - same bat time, same bat channel.
Members, you can watch it right now because I'm gonna keep recording.
But that's gonna do it for your TechHelp video for today.
Hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for part two.
TOPICS:
How to optimize Access lookups beyond DLookup
Problems with using DCount to check for record existence
Using DLookup with NZ to check for existing records
Performance impact of repeated DLookup calls
Using direct SQL queries in VBA for lookups
Opening a recordset in dbOpenSnapshot mode
Using TOP 1 in SQL to return a single record
Checking rs.EOF to determine if a record exists
Comparing DLookup method vs SQL recordset method
Adding Timer code to measure VBA code speed
Testing performance of DLookup in a form
Implementing a lookup using DLookup and NZ
Displaying status messages with StatusBox
Linking to external Access tables for large datasets
Building a form button to perform a DLookup
Passing string values safely in DLookup criteria
COMMERCIAL:
In today's video, we're learning about speeding up your Access database by finding a faster alternative to DLookup for checking if records exist. I'll explain why DLookup and especially DCount can slow down your forms, where it's okay to use them, and when it's time to use more advanced methods. You'll see a demo on how I check for emails using DLookup and how to time how long it takes, setting the stage for tomorrow's lesson where we look at a much faster SQL recordset approach. If you want your Access forms to run smoother, especially with big tables, this is for you. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. Why might DLookup perform slowly when used repeatedly in a form's OnCurrent event?
A. Because DLookup is not optimized for speed and re-queries the table each time
B. Because DLookup locks the entire database during use
C. Because DLookup only works with small tables
D. Because DLookup always returns all fields from the record
Q2. When you only need to check if a record exists in a table, which method is recommended over DCount, and why?
A. Using DLookup, because it stops at the first match instead of scanning all records
B. Using DCount, because it is faster on large tables
C. Using DSum, because it returns the sum and existence check at once
D. Using DMin, because it automatically returns null if no record exists
Q3. What is a common mistake developers make when checking if a record exists, according to the video?
A. Using DCount instead of DLookup
B. Using SELECT * in SQL
C. Using DBOpenTable instead of DBOpenSnapshot
D. Using NZ on every value regardless of data type
Q4. What is the advantage of using a direct SQL query with TOP 1 in combination with a recordset?
A. It retrieves only the necessary record, making the lookup much faster
B. It retrieves all records from the table for thorough checking
C. It automatically updates the record as well
D. It reduces database size
Q5. In the context of VBA and Access, what does the NZ function do in combination with DLookup?
A. Converts null values to a specified default value
B. Forces the query to run locally
C. Sorts the records in alphabetical order
D. Counts the number of non-null entries
Q6. When optimizing performance-critical parts of an Access application, what tool is suggested as a replacement for repeatedly-used DLookup calls?
A. Recordset-based lookups with SQL and TOP 1
B. Using aggregate queries
C. Batch updating all records in advance
D. Running DCount twice
Q7. Which of the following is NOT a prerequisite skill for applying the advanced lookup optimization taught in the video?
A. Understanding recordsets in VBA
B. Basic knowledge of SQL SELECT statements
C. Familiarity with the NZ function
D. Mastery of advanced database encryption
Q8. Why is using DCount for existence-checking considered inefficient?
A. Because DCount scans all matching records before returning a result
B. Because DCount cannot be used on large tables at all
C. Because DCount always locks the table
D. Because DCount only works on numeric fields
Q9. In the code example, what is the purpose of the line:
messageID = NZ(DLookup("MessageID", "MessageArchiveT", "SenderEmail = ' & Email & '"), 0)
A. To retrieve the ID of any message for a particular email, or 0 if none found
B. To count the number of messages for the email
C. To delete all messages for the email
D. To get the latest message date for the email
Q10. What is the point of timing code execution using the Timer function, as demonstrated in the video?
A. To identify slow parts of your code for optimization
B. To prevent timeouts from occurring in loops
C. To synchronize two databases
D. To keep track of system time for logging
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-D; 8-A; 9-A; 10-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone is geared toward the advanced Access developer. In this session, I am going to discuss how you can perform database lookups significantly faster than with the traditional DLookup function. While DLookup is a favorite of mine and extremely handy, its speed leaves something to be desired, especially when you are working with large datasets or calling it repeatedly, such as in a form's OnCurrent event.
If you find that your forms are slow to load or your code is sluggish, this tutorial is for you. Yesterday, I covered how to use the Timer function to pinpoint exactly which parts of your code are causing delays. For those who want to isolate slowdowns within, for example, a lengthy OnCurrent event, you can insert Timer statements before and after different code sections to measure their impact on performance. Once you know where the bottlenecks are, the next step is to optimize them, and that's what we'll be focusing on today.
I want to reiterate an important observation from a previous lesson: if you only need to confirm whether a record exists, do not use DCount. Using DCount is a common mistake and is much slower than using DLookup for this purpose, because DCount processes all records in the table, which is wasteful. Instead, use DLookup to pull just one field—typically the ID—and then wrap this call in an NZ function to detect a null. If it returns something, you know the record exists, and it is much faster than counting all the records.
Those of you who have followed my content know how much I rely on DLookup and the other D functions like DCount, DMax, DMin, and DSum. They are useful and straightforward, but they come with performance caveats. The D functions are not optimized for repeated or intensive use. For example, if you are performing half a dozen DLookups every time a customer form loads, as I often do to check various customer-related statuses, things can get sluggish quickly.
For tasks like displaying a total with DSum on a dashboard or infrequent lookups in forms that are rarely opened, the D functions are perfectly fine. But when speed matters—such as forms you use all day, or code that loops intensively—it's worth reaching for more advanced solutions.
This leads to the main technique I want to introduce today. The solution is to bypass the D functions and use a SQL query in your VBA code to open a recordset directly. With dbOpenSnapshot mode and a "TOP 1" clause, you can efficiently retrieve just one record and check if it exists. This approach is extremely fast, often reducing operation times by half or more. I have had forms that used to open in eight seconds load in just four using this method. Multiply that savings over frequent use, and you have a significant improvement.
Do not worry, though—you do not have to rebuild your database or eliminate DLookup everywhere. This technique is best reserved for sections of your application where performance is critical, like heavily used forms or code that runs in loops or OnCurrent events. Personally, once I developed my own fast DLookup function based on this principle, I started using it whenever I needed a quick lookup, and the benefits became clear as my database and user base grew.
If you are new to these advanced concepts, I recommend familiarizing yourself with some prerequisites first. Make sure you are comfortable with VBA basics such as declaring and using variables, control structures like If-Then-Else, and most importantly, recordsets and basic SQL statements. You should also be familiar with all the D functions, especially DLookup, and the NZ function for handling nulls. I use my custom StatusBox function to provide feedback in the user interface, instead of relying on MessageBox or the Immediate window—if you're not familiar with that, there is a video for it as well.
All the tutorials for these foundational skills are freely available on my website and YouTube channel. If you feel uncertain about any of these topics, I encourage you to review them before continuing.
For this demonstration, I am using my TechHelp free template, which is available for download if you would like to follow along. In this example, I am working with a very large MessageArchiveT table containing over 200,000 records and occupying about 600 MB. This is ideal for putting these performance techniques to the test. I want to check whether any emails exist in this archive for a given customer.
To illustrate, I add a button to my customer form to check if the person has any emails in the MessageArchive table. I start with the traditional DLookup approach, where I look up the message ID based on the sender's email. If no record is found, NZ will return a zero; otherwise, I know there are emails. I then use my StatusBox to present "Emails were found" or "No emails found" based on the result.
I also time the operation using the Timer function to measure exactly how long the lookup takes. In my test, the lookup took about a quarter of a second—not too bad for a single lookup, but if you are running several of these every time a record changes, that impact starts to add up quickly.
This establishes a baseline for performance. Next, in the following lesson, I will demonstrate the faster, optimized method using a recordset and SQL. This is the approach you should use for heavily used forms and parts of your application that require optimal speed. If you are a member, you can watch the continuation immediately; otherwise, stay tuned for part two in an upcoming video.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List How to optimize Access lookups beyond DLookup
Problems with using DCount to check for record existence
Using DLookup with NZ to check for existing records
Performance impact of repeated DLookup calls
Using direct SQL queries in VBA for lookups
Opening a recordset in dbOpenSnapshot mode
Using TOP 1 in SQL to return a single record
Checking rs.EOF to determine if a record exists
Comparing DLookup method vs SQL recordset method
Adding Timer code to measure VBA code speed
Testing performance of DLookup in a form
Implementing a lookup using DLookup and NZ
Displaying status messages with StatusBox
Linking to external Access tables for large datasets
Building a form button to perform a DLookup
Passing string values safely in DLookup criteria |
