Faster DLookup 2
By Richard Rost
9 months ago
Speed Up Code & Forms with Simple Technique Part 2
In this Microsoft Access tutorial I will show you how to optimize your code for faster lookups by comparing traditional DLookup with a more efficient recordset method using SQL and dbOpenSnapshot. We will build a sample button to implement each technique, measure execution times, discuss indexing for speed, and explain why recordsets are generally faster and more scalable than repeated DLookup calls. This is part 2.
Members
In the extended cut, we will build several faster custom functions using record sets instead of DLookup and DCount, including MyDLookup, MyDCount, MyDExists, and MyDValueList. I will show you how to make each of these, and demonstrate how MyDValueList can return multiple fields as an array from a single call.
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!
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, Faster DLookup, recordset vs DLookup, email archive table, OpenRecordset, TOP 1 SQL, dbOpenSnapshot, indexing primary key, performance optimization, MyDLookup, MyDCount, MyDExists, MyDValueList, variant array, code vault, bottleneck, database speed, Data Access Objects, query plan caching, timing function, load form faster, advanced developer, high-traffic areas
Intro In this video, we continue exploring ways to speed up DLookup operations in Microsoft Access by using DAO recordsets in VBA. I will show you how to replace DLookup with a more efficient recordset method, write optimized SQL queries with SELECT TOP 1, apply proper field indexing, and choose dbOpenSnapshot for faster read-only access. We will benchmark the performance difference between traditional DLookup and recordsets, discuss the impact of repeated lookups on form load times, and talk about best practices for database optimization and cleanup.Transcript Today's part two of my Faster DLookup series. If you haven't watched part one yet, go watch it first and then come on back.
All right, so yesterday we made code in here to check to see if this person has any messages in the email archive table, the message archive table. So we used the old-fashioned DLookup method. Now let's do it again with the newer, faster method.
All right, so let's make another button. We'll call this one DLookup. All right, we'll copy and paste that guy. We're going to call this one the record set. OK, because we're going to use a different method.
Ready? Right click, build event. We're going to do the same thing, but we're going to optimize this one for speed. Built for comfort, built for speed.
OK, here we go. All right, so first we need:
Dim RS as a Recordset
We're going to set RS equals CurrentDB.OpenRecordset. And if you have multiple record sets you're going to be open, make sure you Dim a db variable as Database because it'll be faster than having to open multiple database objects.
All right, so now we're going to put an SQL statement in here that's kind of the same thing as what we did above. We're going to go:
SELECT
Here's the important thing: TOP 1. That tells the query engine, just get me one record. Otherwise, it's got to just go through a bunch of records. No, I just want one record.
OK, then MessageID. Don't put a star here because a star says bring back all the fields. Again, slower. You just need one field, preferably the primary key, and definitely make sure it's indexed, which it should be if it's a primary key.
All right, FROM MessageArchiveT. Let's continue it to the next line.
All right, WHERE? What do we add? Sender. It's the same thing as if you're just going to copy this guy. Copy all of that. Paste.
And definitely make sure that this is indexed too. Index, no duplicates if you need to. All right, you want to index everything you want to speed up. Indexing is extremely important. Watch this video if you want to learn more about indexing and how important it is for a fast database.
All right, so we got that with sender email. Here comes the next important part, comma, dbOpenSnapshot, and the closing parenthesis.
Oh, I missed something. What did I miss? Oh, I got an extra quote there. I knew something was up when I didn't get my IntelliSense when I hit this comma. There you go.
Type, and it doesn't give you the IntelliSense of the list here. There's dbOpenSnapshot, there's dbOpenTable, there's dbOpenDynaset. There's a whole bunch of different ones. Snapshot just means it's read-only and it's the fastest one.
OK. Normally, if you open a record set, it opens up in dynaset mode so you can read and write records.
Now, for this example, all we care about is whether that returns a record or not:
If RS.EOF Then
Let me copy the rest of this stuff up here. Wait, here we go. Err, copy. Paste.
If we're at the EOF or the BOF - but we'll be at the EOF if there are no records. You really only need to worry about BOF if you're going back and forth through a record set. If there's no records, BOF and EOF will both be true.
No emails found. Otherwise, emails were found.
OK. Now, RS.Close.
And remember, if you set it, you got to forget it. And yes, that's on a t-shirt now. Set RS = Nothing. You got to get rid of it.
OK. Now, let's make sure this works. Save it. Debug, compile once in a while. Come back up here. Hit the record set button. I did some tests over here. Let me get rid of this stuff. Pay no attention to the man behind the curtain.
OK. All right. Ready? DLookup. There we go. 0.34 seconds. Hit the record set button. Boom. Oh, we didn't put the timing in there. Well, it's working. Emails were found.
Let's put the timing in here. We got to track the timing too. We need t as a double, and t equals Timer at the beginning. So we'll do that just like we did before.
All right. We're going to use an empty space, and then at the bottom, we need the time elapsed. Copy and paste.
All right. Now we'll be able to see the difference in time.
All right. Let me get rid of this stuff again. In fact, let me do this before then. And the DLookup right here, when we start this, I'm going to put StatusBox = blank so it clears the StatusBox when we're on the first one.
All right. Ready? Here we go. DLookup. Oh... OK. Yeah, OK. It's on the main menu. My bad. This has to be Forms!MainMenu!fStatusBox.
OK. I'm sure this time. Here we go. Ready? Debug, compile. Go back over here. Click it. All right. 0.27. And then the record set. 0.10. Look at that. Much, much slower. No. Faster. The record set's faster than DLookup.
Let's do it again, make sure it's not a quirk. 0.14. That's faster that time. 0.16. That one's actually slower that time. It's not going to be exact every time. You're just looking for long-term results. Sometimes it will run a little slower.
So let's do it again. DLookup? 0.3. Record set? 0.07. DLookup? 0.13. 0.17. All right. 0.13. 0.10.
Nine times out of ten, record sets are going to be a lot faster. See right there. DLookup? 0.23. 0.07. 0.32. 0.15. See? Sometimes you'll get a fluke depending on what other stuff is going on and the computer's memory. Usually, 0.15, 0.14. Sometimes they're about the same. That was really fast. That was even faster. 0.18, 0.06.
OK. As you can see, generally, this one's a lot faster than this one. Generally, there are exceptions. 99% of the time I might say that this is going to be slower than this. There you go.
All right. So we've seen it's faster. Now for the nerds, let's talk some theory here. Even though both are doing the same job, the record set is faster.
When you use DLookup, Access has to parse your parameters, resolve references, compile the SQL under the hood, and then execute it every time. It does all of that every time. Even if you call it multiple times in a row, it doesn't reuse anything. It rebuilds the query plan every time.
Convenience comes at a cost. And let's say we only check to see if something existed or not. If you're pulling in values, like if you actually wanted that ID, what the ID was, right here, you could say Status, you know, "The ID is," and you could say RS!MessageID instead of "Emails were found."
All right. So you can retrieve values here too. And now when you run it, you get the ID. All right. The ID is 630996. One ID in that table happens to be that.
Now, if you need four fields out of that table, if you need the ID, the first name, the last name, the email address, the message, the body, and you did five DLookups, that's going to be tremendously slower than opening up the record set once and pulling in five fields.
OK. Now with the record set, we write the SQL ourselves. We say SELECT TOP 1, and the TOP 1 tells the database engine to stop after the first match. Combine that with dbOpenSnapshot, which is optimized for read-only, and we skip all the overhead of write tracking and record locking. It's just lean, clean, fast. The one-way read is nice and quick.
Best of all, we're talking directly to the DAO engine, the Data Access Objects engine, which is the engine underneath Access. There's no abstraction, no extra layers. It's faster, it's predictable. That's better for repeat use.
DLookup is great for small things, but performance wise, it doesn't scale. Every time you call DLookup, Access compiles and runs a new query. No reuse, no plan caching, no batching. You need five separate values, that's five separate queries. A record set doesn't do that.
We pass one SQL string with TOP 1, tell the database engine to give us one row, and don't track changes. That cuts down on memory and record locking. The result is you get faster execution.
Now if record sets are better, why didn't Microsoft just optimize DLookup like that? Well, the simple answer: it was never really designed to be fast. It was created for ease of use. It works great in control sources, macros, quick conditional logic. It doesn't require you to know SQL or DAO, which is record set language. But under the hood, it builds and tears down a full query every time. No reuse, no caching.
That's fine for small forms, once in a while queries, but when you start tracking them in OnCurrent events and you have tons and tons of them, it adds up fast, that little bit of overhead as we've seen.
Like I said before, I tested this on my own main customer form. I replaced three or four DLookups and DCounts with snapshot-based equivalents. Before, it took eight seconds to load, and now it's down to four, and I'm still working on it. I still have some optimizations to do. Four seconds to load a form is too long. I might offload some of that stuff.
One specific lookup went from 0.38 seconds, on average - I ran it a hundred times to get the average - and I dropped it down to 0.208. That's a 48 percent improvement. I mean, that's not huge on its own, but you stack a few of those up, and this is a form that I use all the time. Little wins, big saves. How does that saying go?
All right. So in summary, you don't have to rebuild your whole app. Just clean up the high-traffic areas. That's where DLookup becomes a bottleneck. Snapshot record sets with SQL, TOP 1. It's a little more work, but for power users and advanced developers, it's definitely worth it.
This is something I do once in a while when I've got a form or a button that just runs slow. I throw some timers in it. I see what's causing the bottleneck, and then I replace it.
Now, at this point, you're probably thinking, "Well, DLookup is a nice function that's wrapped up and easy to use. That's a lot of work opening up this record set, remembering TOP 1 and the snapshot. Couldn't you make a function to do this instead of using DLookup?"
Yeah, you could. And in the extended cut for the members, we're going to do just that. We're going to make a function called MyDLookup. It's going to look exactly like DLookup to the outside world, but inside, it's going to do all the cool stuff that we just did.
And we're going to go over MyDCount, which is a faster DCount version that uses record sets to loop through the records, instead of the slow DCount.
MyDExists, which is optimized even further to just check to see if it exists or not.
And this is my favorite one, MyDValueList. This will allow you to send a comma-separated list of fields, so you could say, in one shot, "Give me first name, last name, email address," and it'll return them in an array. That's super cool.
Here's mine that I built, and I'm going to go over this one in the extended cut. And here's my Run Test button. I put it on the main menu, and you could see here, I got loops, and then it does Run Test. And here's Run Test down here.
It does the same thing. First, we test DLookup, we're using the actual DLookup function. Then we test MyDLookup and see if it's faster. Then we test DCount against MyDCount.And then we test to see if it exists, which is just a DLookup against my DExists. And then here's the value list thing. A value list is much, much faster, because with DLookups, you have to make three separate lookups, whereas my DValueList, you send it a comma-separated list like that, and then you get back an array. A variant array. Pretty cool stuff.
It's been a while. Value list. I built this for myself first, because I wanted it for optimizing my database. And then I thought I might as well throw it together and make a video out of it, because I'm sure all of you can use some help optimizing your databases as well. Make them run faster.
Well, that's what we're going to do in the extended cut for the members. Silver members and up get access to all of my extended cut videos. We have around 400 of them now. There is lots of stuff to watch for days, weeks, minutes, and seconds. All those other units of time.
Gold members, you can download all this stuff, and all of these functions will be in the code vault on the website. So if you're working two months from now and you need that faster DLookup, just go to my website and type in DLookup, and there it is. It's in the code vault.
And of course, if you want to sharpen your skills even further, I've got tons of developer lessons on my website. I'm up to around 52 now and cover all kinds of stuff. We're working on class modules right now. That's some fun stuff, so check it out. There's a link. I put a link down below.
But that is going to do it. That's your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time, and members, I'll see you in the extended cut.
If you enjoyed this video, hit that thumbs up button right now and give me a like. Also, be sure to subscribe to my channel, which is completely free, and make sure you click that bell icon and select all to receive notifications whenever I post a new video.
Do you need help with your Microsoft Access project? Whether you need a tutor, a consultant, or a developer to build something for you, check out my Access Developer Network. It's a directory I put together personally of Access experts who can help with your project. Visit my website to learn more.
Any links or other resources that I mentioned in the video can be found in the description text below the video. Just click on that show more link right there. YouTube is pretty good about hiding that, but it's there. Just look for it.
Now, if you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of Microsoft Access, including building forms, queries, reports, tables, all that stuff. It's over four hours long. You can find it on my website or my YouTube channel. I'll include a link below you can click on. Did I mention it's completely free? If you like Level 1, Level 2 is just one dollar. That's it. And it's free for members of my YouTube channel at any level.
Speaking of memberships, if you're interested in joining my channel, you get all kinds of awesome perks. Silver members get access to all of my extended cut TechHelp videos, and there are hundreds of them by now. They also get one free beginner class each month, and yes, those are from my full courses.
Gold members get the previous perks plus access to download all of the sample databases that I build in my TechHelp videos. Plus, you get access to my code vault where I keep tons of different functions and all kinds of source code that I use. And gold members get one free expert class every month after completing the beginner series.
Platinum members get all of the previous perks plus they get all of my beginner courses, all of them from every subject, and you get one free advanced or developer class every month after finishing the expert series. And you can become a diamond sponsor and have your name listed on the sponsor page on my website.
So that's it. Once again, my name is Richard Rost. Thank you for watching this video brought to you by AccessLearningZone.com. I hope you enjoyed it. I hope you learned something today.
Live long and prosper, my friends. I'll see you next time.
TOPICS:
Creating a faster alternative to DLookup with recordsets
Using DAO recordsets in VBA for lookups
Writing optimized SQL queries with SELECT TOP 1
Choosing specific fields instead of SELECT * for speed
Understanding the importance of indexing fields
Comparing dbOpenSnapshot vs dbOpenDynaset
Checking for results using RS.EOF in VBA
Retrieving data from a recordset
Closing and cleaning up recordset objects
Timing and benchmarking DLookup vs recordset speeds
Clearing and updating a status box in VBA
Impact of repeated DLookup calls on performance
Performance differences with multiple lookups
Accessing field values from a recordset
Reducing form load times by replacing DLookups
Summary of pros and cons of DLookup and recordsets
Using SQL and SNAPSHOT recordsets to minimize overhead
When to prefer DLookup for simplicity
When to replace DLookup for high-traffic areas
General optimization strategies for Access forms
COMMERCIAL:
In today's video, we're continuing with part two of the Faster DLookup series. You'll learn how to use record sets in Access VBA to create a quicker alternative to DLookup, including using SQL with TOP 1, dbOpenSnapshot, and proper indexing to boost your database performance. We'll compare the speed differences between DLookup and record sets, cover when each method is best, and discuss practical ways to optimize your code. In today's Extended Cut, I'll show you how to build custom functions like MyDLookup, MyDCount, MyDExists, and MyDValueList for even faster lookups and return multiple fields at once. 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. What is the main reason the recordset method is generally faster than DLookup for checking if a record exists?
A. Recordset avoids rebuilding query plans and compiles once per use
B. Recordset automatically indexes the table for faster lookups
C. Recordset compresses the table before executing queries
D. Recordset does not require any knowledge of SQL or DAO
Q2. When opening a recordset to quickly check for an existing record, which SQL clause was emphasized to improve performance?
A. WHERE 1=1
B. TOP 1
C. ORDER BY
D. GROUP BY
Q3. Why should you use the primary key or an indexed field in the SELECT statement when performing fast recordset lookups?
A. To ensure referential integrity
B. To avoid duplicate records
C. For faster lookups due to proper indexing
D. To retrieve more information in one query
Q4. What does dbOpenSnapshot mode in a recordset signify?
A. The recordset is read-only and optimized for speed
B. The recordset allows edits and additions
C. The recordset locks records for other users
D. The recordset is linked to a spreadsheet
Q5. What disadvantage of DLookup is frequently highlighted in the video?
A. DLookup cannot work with indexed fields
B. DLookup is complicated to use in macros
C. DLookup rebuilds and executes a new query every time
D. DLookup requires you to write SQL manually
Q6. If you need to fetch five different fields from a table, what is the performance implication of using five DLookup calls?
A. It runs much faster because DLookup is optimized for multi-field use
B. It performs five separate queries, resulting in much slower performance
C. It automatically batches the queries for efficiency
D. It only runs one query and caches the rest
Q7. Which method communicates directly with the DAO engine for optimal speed and predictability?
A. DLookup
B. Recordset using SQL and dbOpenSnapshot
C. VBA arrays
D. Linked Excel tables
Q8. What is the suggested way to further optimize repeated use of DLookup and DCount in a database?
A. Rebuild the whole application in a different language
B. Replace high-traffic DLookup/DCount areas with snapshot-based recordsets
C. Turn off all indexing in the database
D. Use more macros in forms
Q9. In the context of the tutorial, why might you choose to design your own function, such as MyDLookup or MyDCount?
A. To create confusing code for other users
B. To wrap up optimized recordset logic for repeated use and easier coding
C. To require manual indexing every time
D. To avoid learning SQL syntax
Q10. What important step must you do after using a recordset in VBA to avoid memory leaks or locking issues?
A. Compact and repair the database
B. Set RS = Nothing after closing the recordset
C. Reboot the computer
D. Delete the database object
Q11. Why was adding a timer variable ('t as double, t = Timer') useful in the demo?
A. It synchronized different databases
B. It measured the elapsed time to compare performance
C. It prevented multiple users from running the code
D. It was required by the Access engine
Q12. What is one of the key advantages of building your own value list function like MyDValueList?
A. You can fetch several fields with a single query and return them all at once
B. You can only retrieve numeric fields
C. It is less reliable than multiple DLookup calls
D. It disables indexing on the table
Q13. What does the video recommend focusing on first when optimizing an application for speed?
A. Redesign all reports from scratch
B. Clean up high-traffic areas where lookups cause a bottleneck
C. Reinstall Microsoft Access
D. Convert Access tables to Excel
Q14. In what scenarios does the speaker suggest DLookup is still appropriate?
A. Large batch operations on big tables
B. Once-in-a-while lookups in small forms or quick conditional logic
C. Exporting whole tables to CSV
D. Automatic form refreshing
Q15. What is one of the crucial concepts regarding index usage in speeding up database lookups?
A. Indexes are only necessary on text fields
B. Indexes should be disabled to avoid overhead
C. Proper indexing of lookup fields speeds up query execution significantly
D. Indexes automatically double database size
Answers: 1-A; 2-B; 3-C; 4-A; 5-C; 6-B; 7-B; 8-B; 9-B; 10-B; 11-B; 12-A; 13-B; 14-B; 15-C
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 continues my discussion on improving the speed of DLookup in Microsoft Access. This is part two of my Faster DLookup series, so if you haven't already watched part one, I recommend reviewing that material before moving ahead here.
Yesterday, we created code to check for messages belonging to a person in the email archive table using the traditional DLookup method. Today, I'm introducing a faster approach using record sets.
First, I added another command button on the form to demonstrate both methods side by side. One button still uses the classic DLookup approach, while the new one will rely on opening a record set. For those of you familiar with VBA, I'll point out some technical considerations as we go.
The record set method involves declaring a Recordset variable and then opening it with CurrentDB.OpenRecordset. If you plan to open multiple record sets, consider declaring a Database variable as well; this can save time since you won't need to instantiate several database objects.
The heart of this faster approach is composing your SQL statement to return only what you need. Instead of retrieving all columns, you select just the primary key field - in this case, MessageID - and make sure to use the TOP 1 clause. By telling the query engine to fetch only a single record, you reduce how much data Access needs to process. It's also crucial that any fields you filter or search on, like the sender's email, are indexed for performance. Indexing is a significant factor in database speed.
When opening the record set, you want to specify dbOpenSnapshot as the type. Snapshot mode is read-only and optimized for speed; it avoids the overhead of tracking changes or record locking that comes with other modes like Dynaset.
After opening the record set, the code checks whether there are any records returned by looking at the EOF property. If the record set is empty, this confirms no emails were found. If not, it means at least one email exists for that user. Always close and release your record set objects after use.
To properly compare the two methods, I included performance timing code. This way, we can track the actual execution time for each approach and observe which is faster in practice. You might see occasional anomalies in the timing due to other processes or memory usage on your computer, but generally, consistently lower times mean a more efficient method.
Upon repeated tests, the record set approach is nearly always much faster than DLookup. While there may be rare cases where they're about even, typically, using record sets gives a significant performance boost.
Now, let's talk a little about why this is the case. When you use DLookup, Access must interpret your parameters, parse references, compile the SQL syntax, execute it, and then do it all over again each time you use DLookup. There's no caching or reuse; every call is a fresh execution with all of the associated overhead.
DLookup is handy for simple tasks and control sources where you need a quick value, but its convenience comes at the cost of performance when called repeatedly. If you need to retrieve multiple fields, DLookup is even less optimal, since you might need to call it several times. In contrast, with a record set, one call can retrieve as many fields as you want in a single shot.
The record set approach, where you control the SQL statement and use TOP 1, lets the database engine stop searching after it finds a single match. Using dbOpenSnapshot further reduces unnecessary features, making the operation rapid and direct. Since you're interacting directly with the DAO (Data Access Objects) engine, there is no added abstraction or hidden overhead.
Microsoft designed DLookup for simplicity, not speed. It's accessible and doesn't require knowledge of SQL or DAO, so it's suitable for quick lookups in forms or reports. However, if you rely heavily on DLookup in event-driven locations such as OnCurrent, and you have dozens of these calls, that overhead adds up fast.
Personally, when I identified speed issues on my main form, I replaced several DLookups and DCounts with their snapshot record set counterparts. The result was cutting my form load time in half. For a single lookup, the improvement might seem minor, but across an entire application, small gains compound to make a considerable difference.
In summary, you don't have to rewrite your entire application. Focus on the areas where performance is most impacted, such as busy forms or repeated queries. Substitute DLookup for tailored record set snippets using SELECT TOP 1 and dbOpenSnapshot, and you will usually see things run significantly faster.
Now, you may be wondering whether you could encapsulate all this into a custom function to replace DLookup for convenience. The answer is yes, and that is exactly what I cover in today's Extended Cut for members. I will show you how to build functions like MyDLookup and MyDCount, which mimic the functionality of their built-in counterparts but use faster record set techniques internally. There's also MyDExists for rapid existence checks and a function called MyDValueList, which can return multiple field values as an array from a single query.
In the Extended Cut, I demonstrate these custom functions and how to use them for timing tests to compare their speed against traditional methods. All of these routines are available in the code vault for Gold members, so you can easily access and reuse them in your own projects whenever needed.
I also encourage you to take advantage of other resources on my website, such as comprehensive developer lessons and courses that cover all levels of Access use, including advanced topics like class modules.
That wraps up this TechHelp lesson. You can find a complete video tutorial with step-by-step instructions covering everything discussed here on my website at the link below.
Live long and prosper, my friends.Topic List Creating a faster alternative to DLookup with recordsets
Using DAO recordsets in VBA for lookups
Writing optimized SQL queries with SELECT TOP 1
Choosing specific fields instead of SELECT * for speed
Understanding the importance of indexing fields
Comparing dbOpenSnapshot vs dbOpenDynaset
Checking for results using RS.EOF in VBA
Retrieving data from a recordset
Closing and cleaning up recordset objects
Timing and benchmarking DLookup vs recordset speeds
Clearing and updating a status box in VBA
Impact of repeated DLookup calls on performance
Performance differences with multiple lookups
Accessing field values from a recordset
Reducing form load times by replacing DLookups
Summary of pros and cons of DLookup and recordsets
Using SQL and SNAPSHOT recordsets to minimize overhead
When to prefer DLookup for simplicity
When to replace DLookup for high-traffic areas
General optimization strategies for Access forms |
