Do Records Exist?
By Richard Rost
10 months ago
DLookup v DCount v Recordset: Check if Records Exist
In this Microsoft Access tutorial, I will show you how to check if records exist in your database using three different methods: DCount, DLookup, and the recordset approach. You will see how each method works, compare their speed and efficiency, and learn best practices for determining if related records, like customer orders, are present. I will also explain why using domain functions in queries can be slow, and demonstrate faster alternatives with aggregate queries.
Richard from Concord, North Carolina (a Silver Member) asks: I'm deleting a record in my main table, but there are two supporting tables associated with the main record that could have data associated with that record. I wrote this, and it seems to work well enough, but my concern is if it is good code or not.
Members
In the extended cut, we will create our own Dexist function using the fast recordset technique. I will show you how to turn it into a reusable function that returns a true or false value based on whether records exist for the criteria you pass it, just like Dlookup and Dcount.
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, DLookup vs DCount, check if record exists VBA, recordset exists check, compare DLookup DCount recordset, fastest way check if record exists, DCount performance, DLookup performance, NZ function VBA, developer-level VBA lesson, SQL WHERE clause VBA, dbOpenSnapshot, aggregate queries, avoid domain functions in queries, count related records VBA, own de-exist function
Intro In this video, we'll learn how to check if records exist in Microsoft Access using three different methods: DCount, DLookup, and recordsets. I'll show you step-by-step how each technique works, compare their performance, and explain why recordsets are usually the fastest option for large databases. We'll also talk about when to use each method, how to handle Null results with the NZ function, and why it's best to avoid using domain functions in queries. If you want to understand the best way to see if records are related, like checking if a customer has any orders, this video will help.Transcript Today, we're going to write some code to determine if some records exist. For example: does a customer have any orders? Do those records exist or not exist? We're going to look at three different methods: DLookup, DCount, and a recordset, and we're going to see which one performs best.
Today's question comes from Richard from Concord, North Carolina, one of my Silver members. He says, I'm deleting a record in my main table, but there are two supporting tables associated with the main record that could have data associated with that record, like customers and orders, for example. I wrote this, I'll show you in a second, and it seems to work well enough, but my concern is if it's good code or not.
So this wasn't Richard's specific question, but he wanted me to critique his code, and in looking through his code, I discovered he was doing something that could be improved.
I mentioned this in the last Quick Queries video, 36, I think, but I said I was going to expand upon it with a video of its own. So sometimes Quick Queries stuff does become a full video.
In any case, this was the post in his forums, and here's the code that he posted. Basically, what he's doing is, he's asking the user, Are you sure? Are you sure you wanted to delete this? That's wise. Then he's checking to see if there are any related records, and he's using DCount.
Now the problem with DCount and all of the D-Functions, DLookup, DMax, DMin, DSum, all of them, is they're slow. DCount is one of the slower ones, because what you're doing is you're saying, Open up this recordset, table, query, whatever, and I want you to just run down the list and count how many items there are. That is slow; it has to run down the entire recordset.
It would be faster here to use DLookup, because really, you just care if there are any records. You don't really care how many there are. It's just whether there are zero or not zero records. For that, a faster method would be to use DLookup.
So let's go through and see some different methods using DCount, DLookup, and the faster method, because I mentioned in the Quick Queries video, I'd show you an even faster method to check to see if records exist.
Before we do, a couple of prerequisites: this is a developer-level lesson. What does that mean? Well, that means if you've never done any VBA programming before, go watch this video first. There's a little QR code, there's the link, I'll put a link down below, you can just click on it. This will teach you everything you need to know to get started programming in VBA in about 20 minutes. You should know how to use If-Then statements. You should, of course, be familiar with the DLookup function.
We're going to use the NZ function, in case DLookup returns a Null; we want to convert it to a zero. The DCount function, as usual. When we're working with recordsets, which we're going to work with today, you're going to need a little bit of SQL, a basic Select statement with WHERE clauses; that's all you'll need, so go watch this video.
The last method, the fastest method we're going to use, involves using something called a recordset. So if you don't know what a recordset is, go watch this video first. Very important. These are all free videos; they're on my YouTube channel, they're on my website. Go watch all of those, and then come on back.
All right, so here I am in my TechHelp free template. This is a free database; you can grab a copy from my website if you want to. In this database, we have customers, and customers have orders. So let's say you just want to make a little button. You click on it, and it tells you whether or not the customer has orders.
You could take this further. You could make it so that the order button has the order count on it, or it hides itself if they don't exist. You can do all kinds of stuff. But for the purposes of class, we're just going to message box whether or not this guy has orders.
Now let's start with the slowest one, which is DCount.
Design view. Let me shrink that up a little bit. I'm going to copy this button, copy, paste. This guy, we're going to use DCount.
There we go. Name your button, of course. DCount button. Right-click, build event.
This will be our DCount example. We'll need L as a Long. L is going to be DCount, count the records. You could put an actual field in there if you want to. Star just means count all the records. It doesn't matter which field you count, because it's just looking for a record. You could put the ID in here if you want, like OrderID, because if you're using an AutoNumber, you're going to have a value in there.
The star won't count Null values. If you want to just see how many records have a first name, you could put FirstName in here. But if you just want to count the records, put a star in there. That doesn't really change the speed much.
We're looking in OrderT, where the CustomerID equals the CustomerID on the current form. Message box, Customer has L orders.
You could get fancy and make it so if it's one, it doesn't display the S. I've got a whole separate video on how to do that.
This is just plain and simple. So save it, Debug > Compile, come back out here, save it, close it, open it, click, two orders. That's fine; let's make sure. Yep, two orders.
Now, this is fine if you actually want the count of how many orders this customer has. That's what DCount is for. But if you just care whether or not they have any, do orders exist for this customer, then DCount is slow. Any of the D-functions that have to run down the entire recordset - DCount, DSum, DMax, DMin - have to look through all the records. That's going to be slow.
A faster method would be to use DLookup. We can use DLookup and say, if it returns any value, it's going to try to return - just look at one record and see if you can return a value. Look and see if there is an order for that customer.
So let's try it with DLookup.
DLookup. Name your button so Alex doesn't yell at us, DLookup button, and if you don't know that inside joke, you haven't watched enough of my videos.
Build event. This will be our DLookup example.
This time, we're going to look up an ID. I'm going to Dim an ID as Long; same thing. ID is going to be DLookup("OrderID", "OrderT", "CustomerID = " & CustomerID on the current form).
Now, if there are none, this will return an error and a Null value, which you can't stuff into a Long. So we're going to use NZ, the Null-to-zero function, to say, if that's Null, make it zero, because zero can't be a valid ID, because AutoNumbers can't be zero. See how that works?
Now we'll just say, if ID equals zero, then message box "No orders." Else, message box "Customer has orders." End If.
This is going to run much, much faster. I know you're not going to see a difference with this database because this is a tiny database with like 30 orders and it max. But if you're running a big database across a network with thousands of customers and hundreds of thousands of orders, you will see a difference between these two.
Now, let's Debug > Compile, once in a while, come back out here, save it, save it, save it, close it, close it, open it up, and then hit the DCount. This customer has orders. Let's find someone who doesn't. This customer has orders. Go down the chain here a bunch. I don't think Jordy has any. Oh, he's got an order. Jordy's got an order. Oh, okay. Let's see, Janeway. Who doesn't have an order in this database? Let's go to the end. Okay, good. Peregrin Took. "Full of a Took" has no orders.
Customer has no orders.
Now, DLookup is grand. I love DLookup. But again, it's not the fastest way to do this. What is the fastest way to do this? The fastest way to do this is to use a recordset. All the D functions, DLookup, DSum, all of this, have a bunch of overhead with them. Just extra garbage without getting into technical details. They themselves, by their very nature, are slower than if you were to simply open a recordset to the table object yourself and see if there are any records. I'm going to show you the best way to do that.
We're going to come back here, make a third button. Copy, paste. This is going to be Recordset. Recordset button. Right-click, build event. All right.
What's this going to look like? Recordset. We're going to Dim RS as a Recordset. We're going to set RS = CurrentDb.OpenRecordset.
What are we going to open? We need an SQL statement here: Select OrderID from OrderT where CustomerID = CustomerID on the current form.
Now, we're not done. We're going to say: , dbOpenSnapshot. You're not going to get Intellisense for that. You just have to remember it or write it down. dbOpenSnapshot. There are different kinds of recordsets you can open. I don't really talk about them much in the free recordset video, but in my developer class, I go over all of them. A snapshot is essentially a read-only recordset, and it's much, much faster than a normal recordset, which either uses Table or Dynaset, because Access knows it doesn't have to worry about writing to this recordset. It can open it up in a snapshot mode, read-only, nice and fast. Get the data that you want, look stuff up, and then exit out. So that dbOpenSnapshot is going to make it even faster.
So we've got our nice, fast, snapshot recordset open. Now, we're just going to check to see if we're at EOF (End of File). So, if rs.EOF, then if you open a recordset and you're immediately at EOF, that means there are no records. There are actually two markers: BOF and EOF, the beginning of file and end of file. I know they're not files, but these are throwbacks from when we used to work with text files: End of File, Beginning of File. But if you get rs.EOF, that means message box "No orders." Otherwise, you're on a record, message box "Customer has orders."
When you're all done, don't forget to clean up your variables. If you have to set it, you have to forget it. Just remember that. If you have to set it, you've got to forget it. If you just have to Dim it and declare it, you don't have to worry about it. But if anything you have to Set, you want to destroy it. You've got to Set rs = Nothing. You've got to set it, then you've got to forget it. That's a new idea; I just came up with that.
Save it. Debug > Compile once in a while. Close it. Save it. Open it. Ready? Boom. Customer has orders. Let's go back to Peregrin Took. He does not have orders. See?
Now, I know you can't appreciate it here because, again, tiny database, tiny bits of code. But if you use this in a big, big, big database, trust me, you will notice a difference. I've been doing this for years, and I've switched over stuff where I was using DCount to this method, and we're talking about long reports that would take 10-15 minutes to run before, down to like 30 seconds. So, trust me.
If you're working with SQL Server, you can make this a pass-through query, and then it'll run on the server and it'll run much faster. Whereas if you use DLookup or DCount locally, your Access database has to pull all of these records down and do the count. Whereas with this one, it doesn't.
As a reminder, I like to bring this up a lot because I see people do this all the time too: don't throw D functions in queries. They slow things down. For example, let's say you want to make a query with your customers and you want to show how many orders they have.
This is the wrong way to do it: CustomerID, FirstName, LastName, and then over here, we're going to do an order count. I'm going to zoom in. We'll call it OrderCount: DCount("*", "OrderT", "CustomerID = " & [CustomerID]).
This is technically correct, but it's going to run slow on a big, big database. Why is it slow? Because it's got to run through all of the orders for every single row in this result set. What's a better way to do this? Don't use domain aggregate functions in here. Use an actual aggregate query.
Add in that Order table. Change the join if you want to see all the customers and see zeros over here. That's fine. We can change that to a left join. This way, we see all the customers.
Now, make it a total query. Add OrderID and change this to Count. So it'll group by these guys and count the OrderIDs. Boom. This will be much faster than using DCount.
Don't use domain functions like DCount or DLookup in a query or in a continuous form if you can help it. Try not to do that. Make a query underneath and then base this on that query.
If you want to learn more about aggregate queries, go watch this video. If you want to learn more about outer joins--that thing I just did where you changed the join type--go watch this video. Very important. This one's a good one.
If you want to learn more about recordsets and all the complexities and intricacies of those, I start covering them in Developer 16. I spend a lot of time on recordsets in Developer 19. I cover all the different types of recordsets: Dynaset, Table, Snapshot.
I know sometimes people see the topics that are covered like in the top, like Chef's Kitchen Helper, and think, I'm never going to need that. But this is just the example that I use in the video to cover what I want to cover. I do a fun project and in so doing, I teach you that stuff. It's like people see the lesson on letter writer in the Expert series where I teach you how to write letters from your Access database. Yeah, you might not ever need to do that yourself.But I cover a lot of report techniques in those lessons, so they are very important.
Now, members, we are going to take this one step further. We are going to make our own defunction. It is going to be cool. We are going to make our own de-exist function. It is going to use that fast recordset technique, and we are going to make our own function out of it so you can use it anywhere in your database.
Same thing - we will pass it criteria just like we do the lookup and dcount, and we will just return a true or false value, whether or not records exist for that criteria.
This will be covered in the extended cut for the members. Silver members and up get access to all of my extended cut videos, and there are hundreds of them by now.
Oh, wait a minute. Someone is beaming in. Hold on. It is the Borg. Quick. I have to run. Ah, just kidding. But that is going to be covered in the extended cut for the members.
Check it out. If you want to learn more and learn some cool stuff, gold members can download these databases that I build, and everybody gets some free training, so come and check it out.
That is going to do it. Folks, that is your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I will see you next time.
TOPICS:
Using DCount to check for related records
Using DLookup to check for related records
Converting Null to zero with the NZ function
Creating a button to display order existence
Opening a snapshot recordset with dbOpenSnapshot
Using a recordset to check if records exist
Checking EOF property of a recordset
Cleaning up recordset objects with Set rs=Nothing
Comparing performance of DCount DLookup and recordset
Warning against using domain functions in queries
Building aggregate queries to count related records
Creating left joins for queries to show all customers
Counting records in queries using aggregate functions
COMMERCIAL:
In today's video, we're learning about different ways to check if related records exist in your Access database, like whether a customer has any orders. We'll compare using DCount, DLookup, and using a recordset to see which is fastest, and I'll point out why the recordset approach can really speed things up in big databases. You'll see examples of each method, find out when DCount is too slow, and understand best practices for querying related data. If you're ready to write better and faster Access VBA code, you'll want to check this out. 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 drawback of using DCount to check if related records exist in a table?
A. DCount is very slow because it has to count every record that meets the criteria
B. DCount can only be used on numeric fields
C. DCount will always return Null if there are no records
D. DCount cannot be used with SQL Server databases
Q2. When should you use DLookup instead of DCount in determining record existence?
A. When you only need to know if any matching records exist, not how many
B. When you need to count all records that meet criteria
C. When you want to update all matching records
D. When you need to sum numeric values in the records
Q3. Why is the DLookup function faster than DCount for checking if at least one related record exists?
A. DLookup stops searching after finding the first matching record
B. DLookup evaluates the entire table before returning a result
C. DLookup does not support criteria parameters
D. DLookup is always faster because it uses less memory
Q4. Which of the following best describes the fastest method to determine if records exist for a given criteria in a large database?
A. Open a recordset with a SQL SELECT statement and check if rs.EOF is true
B. Use DCount with a star argument and count all records
C. Use an aggregate query with a left join to count rows
D. Use DMin on the primary key of the table
Q5. What is the purpose of using dbOpenSnapshot when opening a recordset to check for record existence?
A. It opens the recordset in read-only mode, making it faster than other modes
B. It makes the recordset updateable by the user
C. It enables automatic calculation of field totals
D. It locks the table for editing during the query
Q6. In the context of Access VBA, why do you set rs = Nothing after finishing with a recordset?
A. To release the memory allocated by the recordset
B. To clear the contents of the underlying table
C. To prevent rs from being used as a variable again
D. To automatically back up the data from the recordset
Q7. If a DLookup operation returns Null, why is the NZ function used in the code example?
A. To convert the Null to zero, which is not a valid ID in AutoNumber fields
B. To display an error message to the user
C. To force DLookup to search again for missing records
D. To sum all matching OrderIDs automatically
Q8. Why is it generally not recommended to use DCount or DLookup in query columns or continuous forms for counting related records?
A. Because domain aggregate functions are slow for large result sets, as they run once per row
B. Because these functions are not supported in Access queries
C. Because they cannot be used with text data
D. Because they always return the same result for all rows
Q9. What is a better alternative to using DCount to count related records in a query with many rows?
A. Use an aggregate query with a join between tables, grouping and counting records
B. Use DMin for each row in the query
C. Use DSum to accumulate record values
D. Use a parameter macro instead
Q10. What is the key benefit of using a recordset with SELECT ... WHERE ... and dbOpenSnapshot for existence checks, especially on large data sets?
A. It avoids pulling all records to the client and checks only for the existence of a match efficiently
B. It always retrieves the entire data set for future processing
C. It guarantees an updateable recordset for editing records
D. It automatically creates backup copies of all related records
Q11. What will rs.EOF return if a recordset opened with given criteria contains no matching records?
A. True
B. False
C. The total count of matching records
D. The first record in the result set
Q12. When designing a function that checks for the existence of any records meeting a certain criteria, what is a best practice for the return value?
A. Return a Boolean True or False value
B. Return the count of all matching records
C. Return the first matching field value
D. Return a Null value if no records found
Q13. In the video, what is suggested as a way to further improve the record existence checking code for reuse throughout the database?
A. Write a custom function using the fast recordset checking technique
B. Put all code in one massive form module
C. Only use DCount in all forms and queries
D. Use parameter macros in all SQL statements
Q14. In the context of field counting, what is the significance of using an asterisk (*) with DCount?
A. It counts all records regardless of field content for the given criteria
B. It returns the sum of all numeric fields
C. It ignores all Null values and only counts non-Nulls
D. It counts only fields that are indexed
Q15. For a customer-orders relationship, if you want to quickly determine if a customer has any orders, which solution is the least efficient in terms of performance on a large data set?
A. Using DCount to count all matches
B. Using DLookup to retrieve one matching OrderID
C. Using a recordset and checking rs.EOF
D. Using a pass-through query on SQL Server
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-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 all about how to determine whether related records exist in your database using three different approaches: DLookup, DCount, and working directly with a recordset. I often get asked which method is fastest or most efficient, especially as databases get larger. Today, I will compare these options and share which circumstances each might be best for.
This question is inspired by someone who was deleting a record in a main table and wanted to check if there were associated records in supporting tables, such as customers and orders. The original solution worked, but it could be improved in both performance and reliability.
While this topic came up in a recent Quick Queries session, today I will be showing you a more in-depth, practical example here.
First, let me mention a few prerequisites. This is a developer-level lesson, so you should already know your way around VBA programming in Access. Ideally, you are familiar with If-Then statements and comfortable with functions like DLookup and NZ, which converts Nulls to zeros. It would also help to understand basic SQL statements, especially SELECT queries with WHERE clauses. If you are new to any of these concepts, I have free videos available on my website and YouTube channel to get you up to speed.
Now, let's work with a sample Access database involving customers and the orders they place. The goal here is to create an interface feature, such as a button, that can tell you whether a selected customer has any orders. I will keep the example simple and have it display a message indicating whether there are any related orders.
Let's start with the traditional approach, DCount. Using DCount, you count the number of records in the orders table for a particular customer. You can use the "*" wildcard to count all records, or if you want to only count those with a specific field populated, such as OrderID, you can do that as well. The key takeaway is that DCount runs through all records that meet the criteria and counts them up. While this approach works fine for small databases or when you truly need the count, it can be slow with larger datasets since it processes every record.
If performance is a concern and all you need to know is whether any matching records exist (not how many), a better approach is DLookup. DLookup simply looks for the first record matching your criteria. If it finds any, it returns a value; if none are found, it returns Null. For example, if you look up OrderID for a particular customer, and you get a result, you know at least one exists. Here, you want to use the NZ function to convert a possible Null result into a zero, since there's no such thing as OrderID zero in AutoNumber fields. This allows you to simply check if the value is zero and react accordingly. This method is much quicker than DCount in large tables, since Access can stop searching as soon as it finds one matching record.
However, even DLookup carries some overhead due to the way domain aggregate functions work in Access. There is an even faster approach: using a recordset. By opening a recordset with an SQL SELECT statement for just the needed records and using dbOpenSnapshot to open it as read-only, you optimize performance further. A snapshot recordset is particularly well-suited when you only want to check for the presence of records. All you need to do is test whether the recordset reaches EOF (end of file) immediately, which indicates that there are no records meeting your criteria. Otherwise, if it is on a record, you know a match exists. Do not forget to clean up your objects by properly destroying the recordset variable once you are finished. This small detail makes a big difference in long-term database stability.
Even if you have only a small amount of data, the speed difference might not be noticeable. But trust me, with thousands of customers and hundreds of thousands of orders, using a direct recordset method instead of DCount can improve performance drastically, particularly with large reports or networked databases.
Now, here is a common pitfall to avoid: using D functions inside queries. Placing DCount or DLookup directly in a query that needs to perform this calculation for each row is very inefficient, because Access will execute the function for every single row it returns. The right way is to use aggregate queries with table joins and grouping, which is what Access is optimized for. For instance, to count the number of orders per customer, use a totals query with a Group By clause and a Count aggregate, rather than DCount.
If you want to learn more about aggregate queries or left joins, I have dedicated lessons covering each of these techniques. Just because a lesson may be organized around a niche example does not mean the broader skills shown are not helpful for your work.
For those who are members, in today's Extended Cut, we will go even further and create a custom VBA function, which I like to call de-exist. This function wraps up the efficient recordset check into a reusable function, returning simply true or false based on whether matching records exist for any criteria you supply. Making reusable code like this is a big step forward for your projects.
If you are interested in downloading example databases, that is a feature for Gold members. But everyone can access my free TechHelp and Quick Queries videos online.
That wraps up today's lesson. 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 Using DCount to check for related records
Using DLookup to check for related records
Converting Null to zero with the NZ function
Creating a button to display order existence
Opening a snapshot recordset with dbOpenSnapshot
Using a recordset to check if records exist
Checking EOF property of a recordset
Cleaning up recordset objects with Set rs=Nothing
Comparing performance of DCount DLookup and recordset
Warning against using domain functions in queries
Building aggregate queries to count related records
Creating left joins for queries to show all customers
Counting records in queries using aggregate functions |
