Speed Up Search
By Richard Rost
3 years ago
Speed Up Wildcard Searches in Microsoft Access
In this Microsoft Access tutorial, I'm going to teach you a trick for speeding up the apparent speed of wildcard searches so that if the user searches for the complete actual value, it will provide results much quicker.
Michael from Council Bluffs, Iowa (a Platinum Member) asks: I love the single-field search button you showed us in one of your videos. I have one minor problem, however. When I use it to search for a field like email address, it takes forever. If I use the actual Access search box and type in the complete email address, it finds it almost instantly. Is there any way to speed up your search?
Prerequisites
Recommended Courses
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, speed up search, speed up wildcard search, speed up find
Intro In this video, I will show you a simple technique to make wild card searches in your Microsoft Access database feel much faster for your users. We will talk about why wild card searches are slow, how indexing affects search speed, and how you can update your search button code to check for an exact match using DLookUp before running a slower wild card search. This easy adjustment can significantly improve the user experience, especially in large databases.Transcript Today I am going to teach you a simple trick to apparently speed up wild card searches in your Microsoft Access database. I say "apparently" speed up because it is all about user perception. We are not going to actually speed up the search that much, but the user will think it is much, much faster if they type in the complete value. You will see what I am talking about in just a minute.
Michael from Council Bluffs, one of my Platinum members, writes in: I love the single field search button you showed us in one of your videos. I have got a minor problem, however. When I use it to search for a field like email address it takes forever. If I use the actual Access search box and type in the complete email address it finds it almost instantly. Is there a way to speed up your search?
Well, you answered your own question. If you type in the complete email address and you are not relying on a wild card search, it is much, much faster, especially if your field is indexed, which it should be. Anything you are going to search on a lot, you want to make sure is indexed. But as soon as you go into a wild card search where you have to have Access find part of the value, it has to look through all those values. It has to look through all the records, so that is why it takes a lot longer to perform a wild card search.
So what we are going to do is a little trick. We are going to take the code that I gave you in the first video, and then we are going to say, okay, what we are going to do is if the user types in the complete value, we are going to do a DLookUp first and see if it is in the table, which should be relatively quick. If it does not find it, then resort to the wild card search.
Alright, let us see what that looks like. But first, this is a developer level video, so we will be writing some code. This is a follow up to my search button video, so if you have not watched this yet, go watch it. We make a little button here where we can put it next to any field and then search on just that field. If you do not know what indexing is, go watch this video.
Today, we are going to add a DLookUp to our code, so we will make sure you know what a DLookUp is. Now, we are going to use the NZ function to see if we get a null value back.
Alright, these are all free videos. They are on my website, they are on my YouTube channel. Go watch those and come on back.
Okay, here I am in my TechHelp free template. This is a free database you can download from my website if you want a copy. We are going to go into the customer form and make a single field search button for the email address. So, design view. And for the sake of class, I am just going to clear all this stuff out of here. We do not need it. Goodbye.
Okay, next to email address we will put a little button. Do this here real quick. From Design, grab a button and drop it next to email. Cancel the wizard, we do not need it.
Now we will put just a little search here. In the other video I show you how to put a little picture with a magnifying glass. You can do all that if you really want to.
Okay, give it a good name. Come over here to All. This will be btnSearch. Let us call it btnSearchEmail because you can make one for each field if you want to. And, yes, there is a way you can just make one search button, and then you click on the field and then click on the button to look at the last field you were on. I do not like that. I like having one right next to the field you are searching for. That is just my preference. You can do whatever you want.
Alright, let us recreate the code that we created in the last video real quick. So we need a variable "s" as a string. s = InputBox("Enter email to search for", "My Email Search"). The default value we will just leave blank. If s equals blank, if it is an empty string, then Exit Sub. That means the user clicked Cancel or did not type anything in.
Now here is where we do the wild card search. We are searching by basically setting the filter. Me.Filter = "Email Like '*" & s & "*'". Me.FilterOn = True.
At this point, it is going to use the Like and the wild card search with whatever you type in to look through all of the records and bring back the ones that match that. That is great if you are searching for something like a domain. Let us say you want to search for all the people who are at amicron.com, and it will bring those people back. You can see there are a bunch of them because I put all amicron.com email addresses in here.
In some of my older classes, I used made-up email addresses like xyz.com, and I started getting complaints from people at those domains, like "Hey, we own xyz.com. Please do not do that in your videos anymore." I am not saying it was xyz.com, but now all the fake email addresses I use are at one of my domains. I think I did captainpicard or captainkirk at gmail.com one time, and it came back like no.
The downside is, even if this field is indexed, Access still has to go through and pull them all out of the table. Now, I have only got, what, 30 records in a small local database. My main database, I have got like 50,000 customers in it and one hundred and some thousand email addresses on my regular email list. It could take a few minutes.
What you can do instead is, if the user types in the actual email address, like they are copying and pasting it from an email, and they want to look this person up, you can speed this up tremendously by first checking to see if that actual exact email address exists in the table. If it does, go to that one. If not, then do the wild card search.
So how do we do that? Let us go back to our code. We will need another variable, just "id" as a Long. Right down here, after they have typed in what they are looking for, we are going to say id = DLookUp("CustomerID", "CustomerT", "Email='" & s & "'"). We want to handle null values here, so NZ that whole thing so it returns zero if it does not exist.
Now, the DLookUp runs. If it finds that exact email address, it is going to return you the CustomerID. So, if id is not zero, then we are going to say Me.Filter = "CustomerID=" & id. Me.FilterOn = True. Else, Me.Filter = "Email Like '*" & s & "*'". Me.FilterOn = True.
If it finds it, filter based on that id, which is going to be much, much faster. That is one record, got the id already, it is an indexed field, boom. Otherwise, if it does not find it, put on the wild card filter.
I still cannot talk, I am still getting over a cold, and I am going to do a research read tomorrow.
So, okay, are we ready? Let us try it. Save. Alright, again, I will search for, how about 599cd.com. That works, there are three of them. Let me look for exactly mine, right here, [email protected], and there I am, exactly, it found my exact record.
Again, it is hard to see the difference in time with this database, but trust me, I know from experience with my one database that has got 50,000 emails in it, an exact search like that takes less than a second. If I search for a domain like @gmail.com, it takes at least seven or eight seconds. So, this will apparently speed up your user's experience of the search.
Again, make sure your email address field is indexed, either no duplicates or duplicates, depending on what you want. I do not want duplicated emails in my system. If you want that, that is okay. This works for any field you want to search on.
That is it. That is your tip.
Michael, I hope that helps you out. That is going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I will see you next time.Quiz Q1. Why do wild card searches in Microsoft Access typically take longer than exact value searches?
A. Wild card searches require Access to examine every record to find matches
B. Wild card searches use more advanced algorithms that are slower
C. Wild card searches lock the database during the search process
D. Wild card searches automatically disable indexing on all fields
Q2. What is the main benefit of typing the complete value when searching for a field in Access, rather than using a partial value with a wild card?
A. The search is much faster, especially if the field is indexed
B. The search will automatically update related tables
C. The search will create a backup of the table
D. The search will return multiple, unrelated results
Q3. What does the DLookUp function do in Access, as used in this video?
A. Looks up a specific value in a table and returns a result if found
B. Counts the number of records in a table
C. Sorts all values in a specified field alphabetically
D. Deletes a record from a table
Q4. What programming technique is suggested to make searches 'appear' faster to the user?
A. Perform an exact search first using DLookUp, and only use wild card search if no result is found
B. Always use a wild card search regardless of user input
C. Index every field in the entire database
D. Use a separate search form for every possible value in the table
Q5. Why is indexing an email address field recommended if it will be searched frequently?
A. Indexing speeds up exact lookups in the field
B. Indexing will prevent deletions of records
C. Indexing forces unique values, stopping duplicates
D. Indexing automatically emails matching records to the user
Q6. What is the purpose of using the NZ function in the code involving DLookUp?
A. To handle null values returned by DLookUp and avoid errors
B. To format the output as a zero-padded value
C. To convert strings to dates
D. To automatically insert a new row if the value is not found
Q7. In the search button code, what is the purpose of setting Me.Filter and Me.FilterOn?
A. To display only the matching records on the form
B. To sort records alphabetically
C. To permanently remove non-matching records from the database
D. To check for duplicate records before inserting new data
Q8. According to the video, what is the user experience benefit of the suggested search technique?
A. The search 'appears' much faster to the user if an exact match is provided
B. The search always accesses fewer records regardless of input
C. The database reduces in physical size after searching
D. The technique guarantees security against SQL injection
Q9. If the user provides a partial value and there is no exact match, what happens in the code discussed?
A. The code falls back to using a wild card search
B. The search is canceled with no results shown
C. The form reloads to show all records
D. An error message appears to the user
Q10. Why is it not accurate to claim that the method truly speeds up wild card searches?
A. The method only speeds up searches when an exact match is provided, not actual wild card searches
B. The method automatically disables wild cards in all searches
C. The method compresses the database between searches
D. The method skips non-indexed records
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 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 will show you a clever way to give users the feeling of much faster searches when using wildcards in your Microsoft Access database. While we cannot actually make wildcard searches significantly faster behind the scenes, we can greatly improve the user's perception of speed by taking a simple extra step. Users will notice a big difference, especially if they type in the full value they are searching for.
This topic came up thanks to a question from one of my Platinum members, who mentioned loving the search button for a single field (as covered in a previous video). The problem was that searching by an email address, for example, took much longer compared to typing the complete email address in the built-in Access search box, which retrieves results almost instantly. So, the question was, is there a way to improve the speed of the search?
The answer lies in how the search is performed. If you type in the entire email address, and the field is properly indexed, the search can be extremely quick. Indexing is important for any field that you frequently search on. However, the moment you use a wildcard search, looking for part of a value, Access needs to check every record, scanning through the whole table, which slows things down considerably.
To address this, I am going to show you a useful trick. We will start with the code from my original single field search button tutorial. The enhancement involves first checking if the user has entered an exact value that exists in the table. If so, we fetch it directly with a DLookUp, which should be very fast. If not, we fall back on the usual wildcard search.
Let me walk you through how this works. This example is aimed at developers, so we will work with some VBA code. This builds on the search button tutorial where you place a button next to any field and use it to search just that field. If you are not familiar with indexing or DLookUp, be sure to review those free resources on my site or YouTube before proceeding.
If you want to follow along, you can download my free TechHelp template database from my website. I will demonstrate the process using the customer form, adding a single field search button for the email address. In design view, add a button next to the email field, naming it btnSearchEmail. I recommend having a separate search button for each field, but you can adapt this approach as needed.
The code starts by prompting the user to enter an email address. If nothing is entered or the input is cancelled, it exits. For the actual search, the normal technique applies a filter using "Like" with wildcards so that any partial match turns up results. This is useful for finding, for example, everyone with a certain email domain, such as all users with an amicron.com address. Just keep in mind that even with an indexed field, wildcard searches are slow because Access must review every record.
Now, for the improvement. If the user types in the entire email address — perhaps pasting it from somewhere — we can take advantage of DLookUp to see if that exact value exists. If we get a match, we use the record's unique ID to apply a filter, which is very fast and takes advantage of indexing. If there is no such record, only then do we run the slower wildcard search.
To do this, after getting the user's input, we use DLookUp to search for the CustomerID in the CustomerT table where the email matches exactly. We check for a null result using the NZ function so we do not get errors if no match is found. If we get a valid ID, we filter the form by that specific CustomerID, which will immediately show the matching record. If not, we fall back to searching using "Like" with wildcards as before.
You may not notice a dramatic speed improvement in a small sample database, but in a real-world scenario — like my own system with fifty thousand customer records — this approach makes a huge difference. An exact search returns results almost instantly, while a wildcard search can take several seconds. For users, this feels much faster and more responsive.
One final note: ensure that your email field is indexed. You can choose to allow or disallow duplicate values depending on your requirements. Personally, I do not allow duplicates, but this method works regardless of your choice. This technique can also be applied to other fields in your database, not just email addresses.
That is the tip for today. Remember, you will find a complete video tutorial with step-by-step instructions for everything discussed here on my website at the link below.
Live long and prosper, my friends.Topic List How to create a single field search button in Access
Using InputBox to prompt for search terms
Applying a filter with a wild card search in Access
Using Me.Filter and Me.FilterOn for form filtering
Leveraging DLookUp for exact match searches
Combining DLookUp with a wild card search fallback
Using the NZ function to handle null values from DLookUp
Optimizing search performance by checking for exact matches first
Designing a search button specifically for an email address field
Naming and setting properties for a custom button in Access forms
Ensuring the searched field is indexed for performance
Filtering records based on CustomerID for faster access
Filtering records based on partial email matches using Like
Code walkthrough to implement both search methods in VBA |
