Timer
By Richard Rost
11 months ago
Find Access DB Slowdowns Using the Timer Function
In this Microsoft Access tutorial I will show you how to use the Timer function in VBA to measure the performance of your database code with millisecond precision. You will learn how to identify which parts of your code are causing slowdowns, use the Sleep function to simulate delays, compare timing methods using DateDiff versus Timer, and display results in a status box to help troubleshoot and optimize event performance in your Access applications.
Members
There 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!
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, timer function, VBA, sleep function, format function, DateDiff function, database optimization, code performance, status box, On Current event, subroutine timing, milliseconds, floating point, loop optimization, reserved words, idle time detection, event duration, recordset loop, query speed, debugging, event rollover midnight
Subscribe to Timer
Get notifications when this page is updated
Intro In this video, we will talk about how to use the timer function in Microsoft Access VBA to measure the speed of your code and identify slow-running events in your database. I'll show you how to simulate slow processes with the Sleep function, create and time multiple VBA subroutines, measure execution time using both date fields and the timer function for greater precision, and display results in a status box. We'll also discuss handling timer rollover at midnight, differences between the timer function and the On Timer event, and best practices for troubleshooting and optimizing form performance.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor Richard Rost. Today, we are going to talk about the timer function. No, I do not mean the On Timer event. I mean the timer function. Did not know it was there? Yes, it is in there.
If your Access database is running slow, maybe you click a button and it runs a bunch of events - you have some queries and stuff going on in there, some code, some loops - but you are not sure why it is so slow. In this video, I am going to teach you how to drill down inside those events and find out what is running slow, and we will use the timer function to do it, which will let us get millisecond precision on those calculations.
Now, this is going to be a developer-level video. What does that mean? If you do not know VBA and you want to learn it, go watch my Intro to VBA video first. It is about 20 minutes long and teaches you everything you need to know to get started with VBA in about 20 minutes. You should know what variables are. You should watch my video on data types.
We are going to use the format function. We are going to use the sleep function just to simulate an event that takes forever because I am not actually going to make an event that takes three seconds to run, so we will simulate that with the sleep function. We are going to use the date diff function to figure out the difference between two date fields in seconds, and we are going to display our results in my status box. Go watch the status box video. These are all free videos. They are on my YouTube channel. They are on my website. Go watch them, then come on back.
All right, so here I am in my TechHelp free template. This is a free database you can grab off my website if you want to, but you can use pretty much any database you want. Now, I recently had a problem in my own database where, for example, when I open up my customer form, I have a lot more information on there. Every time it goes to a new customer, using the On Current event, several things happen.
For example, it checks to see if there are any emails that this customer sent me that I have to answer, because they all come into my database. So, I am going to put a little red box around the email button so I can click on the email button and go answer the email. It also checks and displays their membership level, because that is not a field in the customer form. It looks to their orders. If they have purchased a silver membership, it will show up in their orders, so I want that to display here too.
Do they have any unpaid orders, like they did not pay their membership fees last month? I kind of want to know about it when I am answering their email. Anyways, it does not happen that much.
When my On Current event runs, or when it did before, it took about four to five seconds. That does not seem like a lot of time, but when you use this form all day long, waiting four seconds every time this form loads, if you go through 100 records in a day and you could shave half the time off of that, each one taking four seconds, you are saving yourself a couple of minutes. Especially if this is something that is running in a loop. If you have got a loop that is running maybe through record sets or whatever, and you have to run through 10,000 records, if you could get that loop down to 20 percent of what it was before - even 50 percent of what it was before - it is going to run a lot faster.
So, the whole point of this is to optimize your database, to drill down and figure out. Today, we are going to figure out what is taking so long, and then over the next week or two, I am going to teach you some other ways we can optimize stuff.
Today, we are just going to figure out - we are going to do some sleuthing, some troubleshooting. First, I am going to make this button run three different subroutines, and they will do three of whatever things - it does not matter - but we are going to simulate some other stuff happening with the sleep timer. So, let us go into this button here.
Let me get rid of this stuff here, clean things up. We do not need these buttons. So instead of Hello World, I am going to say in here I just want to do three things: run sub one, sub two, and sub three. What are these? Well, let us make them.
Private Sub sub1()
' This is literally just going to do some stuff.
Status "sub one is running"
' Maybe it is looking up some stuff, maybe it is running an action query, maybe it is doing whatever.
End Sub
Sub two, sub three - okay. In fact, let me just change this real quick: we will make that sub two and that will be sub three and there we go. We made three subs: sub one, sub two, sub three. Save it.
Let us come back out here and run it. Just make sure it works. Close it, open it, click - boom. All right. Sub two, sub three. And of course, the status box works backwards.
Now, let us simulate these guys taking different amounts of time to run - the query it is running or the code it is executing or whatever the report it is generating. So, this one, let us say: Sleep 1300. This is taking 1.3 seconds to run. Those are milliseconds.
Sleep about 2500. This guy takes two and a half seconds. That is a slow one. And this guy: Sleep 200 - 200 milliseconds.
Now we do not know that, we are just putting that in there to simulate it. All right, click the button, and you can see how now they are taking a little bit longer when they run, and I want to know which one. In fact, let us do this, too. When this starts, let us set StatusBox = "". Let us clear the status box, and then when we are done we will say Status "done" and a beep.
All right, one more time. There we go, it is doing its thing. Now I want to know how long each one of those is taking. What I am going to do is come into my code here and try to figure out, which one of these is taking the longest.
Now, we could just use a date/time field. Date/time fields work fine. You can use them to track how long things run. They only give you times to the second, though, and if you have stuff that is running really long, you know, five seconds, ten seconds, thirty seconds, that will work. Let me show you how that would look first.
We would Dim tStart As Date. Then, before sub one runs, I would say tStart = Now, and then after it is done I would say something like Status "Time to run: " and then we will just do a simple DateDiff - DateDiff("s", tStart, Now). So this will take the difference in seconds between when that loop started and right now when this code executes.
Now we will just copy this to each of the other subs - so sub two and sub three - and then we will just do time to run, basically the same thing, that is, we are resetting tStart each time. Show me this, do that one, show me this, do that one, show me this, do that.
Save it, debug/compile once in a while, come back over here, and let us run it again. Now, we can see that took one second, that took three seconds - it is rounding to the nearest second because the second is as granular as you can get - and that one was zero seconds (not really zero, but okay). Now, that is not too bad, and if you have stuff that is taking a long time to run - I have had some buttons take 30 seconds to run - you can go through line by line in your code and figure out which commands are taking longer.
But if you want a little more precision than that, we can use timer - the timer function. So let us adjust our code accordingly.
Now, tStart instead we are going to call a double. Timer returns the number of seconds since midnight, so it can be up to like 80,000 something. We will talk more about the specific details of timer in just a few minutes, but it is going to be a floating point number, so it is going to have a fractional component, so we are going to store it in double. Remember, I pretty much always use long integer or double unless I'm really really trying to optimize my data. Technically, this returns a single, but we are not going to worry about that floating point number. Use double.
Now tStart = Timer. Yes, it is a function, you could do that, but you do not need to.
Now, time to run is just going to be - here, we are not going to use DateDiff at all because it is not a date value anymore. It is just going to be Timer - tStart, because right here, tStart is going to get set equal to the timer at that second. The sub runs, and now here we are going to say, give me the time to run, whatever the timer is now minus that start time.
The same thing we did before, except instead of using Now, we are just using Timer, which is a number of seconds. So put Timer here. We are going to put Timer here. And we are going to replace that DateDiff with this. This guy was there, and it goes there. Same thing.
Save it. Debug/compile once in a while, come back out, and click the button. And, oh, look at that. Is that not pretty cool?
Now, this is much, much closer to what our actual values are - 1.32. It is going to be slightly different because there is some processing overhead involved, even in my status function. We are talking fractions of a second, though.
But now I can clearly see that query took 1.3 seconds to run. This record set loop took 2.5 seconds. This guy took 0.2 seconds. So now I can say, well, this is the longest part of that event - whatever is in this event, this guy, sub two, is the longest running thing. Let us go into sub two now and figure out what we can do to make this run faster.
Oh, we have got a DCount in there, when all we really want is to know if that table has a record in it. We should be using DLookup instead, or next week I am going to show you something called MyDLookup, which is a faster DLookup that I wrote. That is coming. So, we will talk about optimizing more later.
But for now, we just want to discover what is causing the slowdown, and now we have pinpointed it to sub two. I did this just the other day in, like I said, the On Current event, and I shaved it off from, I think it was like eight seconds at the longest. It is not always going to run exactly the same - certain customers might run longer or shorter depending on how many orders they have or how many emails they have in the system, so it is not exact - but I ran it on a loop and I shaved a lot of time. I cut the event time in half. I think I brought it from eight seconds down to four seconds, so that was pretty good.
Let us talk more about the timer. It is a reserved word. It is technically a function. That means do not use timer as a field name. Do not use name as a field name, that kind of stuff.
Wait. Oh, someone is beaming in, hold on. My man Alex has a page on my website with all the reserved words. I will put a link down below if you want to check that out. I have a video on that I have been working on, so I am going to do a video too, but Alex's page is pretty comprehensive.
Like I mentioned earlier, this is not the same as the On Timer event. The On Timer event can run after a form has been open for so many seconds, or every so many milliseconds technically.
Timer returns the number of seconds since midnight.So there is the value 0 to 86400 is 0. It returns a Single, but you can store it in Double with millisecond-ish precision.
Because I know someone in the comments is going to call me out on it, it is not exactly milliseconds. It is technically one sixty-fourth of a second, or about 15.6 milliseconds. So it is not technically millisecond precision, but it is close enough for anything practical that you are going to do in an Access database.
And yes, again, it is still a floating point number so rounding errors are theoretically possible. But again, it is negligible for practical use, whatever you are going to use this for in Access. It is close enough; you are not building a Mars orbiter here.
Now, if your event does roll over midnight, you have to account for that. It is going to go from 86400 to 0. So, there is some code there you can use for that.
Use Timer for things that are very, very fast, very, very quick. If you have got something running and you think it might possibly run over midnight, then you have to account for it.
Here are some other uses for Timer. I racked my brain trying to think of some things. I even asked Chat GPT, can you think of any other uses for a timer? Detecting idle time. Maybe pausing code execution briefly; again, you can use Sleep for that.
Game logic: if you are building a game in Access, I want to see it. I really want to see screenshots of it - post them in my forums. Those are great. I actually have some other game ideas. We did a hangman a while back. I have some choose your own adventure and dungeon crawl type stuff I want to do in Access. That is one of those side projects.
Smoothing animation for UI delay: Chat GPT came up with that. I do not see how you would use that.
For most of these instances here, I think seconds are just fine. If you really wanted to detect idle time on a form, you do not care if a user has been idle for 0.4 seconds; you care if they have been idle for 30 seconds, 60 seconds, or 90 minutes - that kind of stuff. So, you can just use a date field for that.
There you go, there is Timer. Honestly, this is one of those functions that I almost never use, but it is nice to have in your toolbox because I did have to pull it out the other day when I was wondering why this form was loading so slow. Why is this customer form running so slow when I move from record to record? It should not be, and I figured out why it was. I had some inefficiencies in there.
I have been building my database, my 599 CD database, over the past 20 years. It is the same database file that I started my company with and I have just been adding on to it and making changes, and I have not done a real full redesign ever. I just keep putting new stuff in it. I am like the mechanic that drives the old car. I just keep putting new parts in it and changing out this and that. So it does get optimized, and it works well.
This here is a beast. This is a great function for analyzing your database's speed.
Again, we will be talking more about other things you can do to optimize it once you figure out where the problems are. We will talk about those in upcoming videos.
But that is going to do it for today, folks. I hope you learned something. Live long and prosper, my friends.
I will see you next time.
TOPICS:
Using the Sleep function to simulate slow processes
Creating multiple subroutines for timing analysis
Measuring code execution time with date fields
Using DateDiff to calculate elapsed seconds
Switching from DateDiff to the Timer function
Tracking execution time with millisecond precision
Storing Timer values in Double variables
Displaying execution times with a status box
Identifying slow-running VBA subroutines
Simulating slow events in form buttons
Handling Timer rollover at midnight
Understanding limitations of Timer precision
Comparing Timer and the On Timer event
Best scenarios for using the Timer function in Access
Analyzing slow form or event performance
Pinpointing performance bottlenecks in Access VBA
COMMERCIAL:
In today's video, we're learning about the timer function in Microsoft Access VBA, not to be confused with the On Timer event. I'll show you how to use the timer function to measure the speed of your code, track down which events are slowing down your database, and get precision down to fractions of a second. We'll look at simulating slow events using the Sleep function, comparing the timer function with date-based timing, and displaying results in a status box. Plus, I'll give you tips for troubleshooting and optimizing your Access applications, and we'll discuss some practical uses and limitations of the timer function. 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 primary purpose of using the Timer function as discussed in the video?
A. To create recurring events on forms
B. To measure the execution time of code with high precision
C. To pause code execution for a set period
D. To generate timestamps for logging data
Q2. How precise is the Timer function in measuring time?
A. It is accurate to the nearest hour
B. It is accurate to whole seconds only
C. It offers about 15.6 milliseconds of precision
D. It provides exact millisecond precision
Q3. What data type should you use to store the value returned by the Timer function for best practical results?
A. Integer
B. String
C. Double
D. Boolean
Q4. What is the major difference between the Timer function and the On Timer event?
A. Timer is used for animations, On Timer is only for calculations
B. Timer records system uptime, On Timer detects user inactivity
C. Timer measures elapsed time, On Timer triggers events at set intervals
D. Timer stops code from executing, On Timer only works with reports
Q5. Why is using DateDiff with the Now function less effective for measuring very short code execution times?
A. It is too complicated to set up
B. DateDiff rounds to the nearest millisecond
C. It only measures in seconds, missing fine details
D. It cannot measure code execution time at all
Q6. When using the Timer function, what potential issue must you account for if your code could run past midnight?
A. The Timer will reset to zero at midnight
B. The Timer will begin to count backwards
C. Access will not allow Timer after midnight
D. Timer automatically closes the database at midnight
Q7. The Sleep function was used in the video primarily to:
A. Delay form loading to simulate network lag
B. Pause code as an example of a slow-running event
C. Automatically synchronize Access with Outlook
D. Measure the amount of data processed
Q8. Which of the following is NOT recommended by the instructor when using reserved words like Timer in Access?
A. Using "Timer" as a function in code
B. Using "Timer" as a field name in a table
C. Using "Timer" for measuring time intervals
D. Storing the Timer value in a Double variable
Q9. What is a practical use of the Timer function as suggested in the video?
A. Creating an auto-number field for records
B. Detecting periods of inactivity in a form
C. Automatically closing the database every hour
D. Formatting currency output
Q10. Why is it beneficial to identify which subroutine in your code takes the longest to run?
A. To add more subroutines for improved performance
B. To target optimization efforts where they are most needed
C. To delete all slow subroutines immediately
D. To ensure all subroutines run for the same duration
Answers: 1-B; 2-C; 3-C; 4-C; 5-C; 6-A; 7-B; 8-B; 9-B; 10-B
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 the Timer function in Microsoft Access VBA. I am your instructor, Richard Rost, and in this lesson, I want to introduce you to the Timer function – not the On Timer event that you find in form properties, but the actual Timer function that is built into Access. Many users do not realize it is available, but it can be extremely useful when you are troubleshooting and optimizing slow-running databases.
If you have experienced performance issues in your Access database – for example, pressing a button that runs several events, complex queries, or loops that seem sluggish – the Timer function allows you to pinpoint exactly which processes are taking up the most time. It offers accuracy down to fractions of a second, which is invaluable for diagnosing bottlenecks in your VBA code.
Before we get into the details, I want to mention that this is a developer-level tutorial. If you are not yet comfortable with VBA, I recommend you first watch my 20-minute Introduction to VBA, as well as my video covering VBA data types. Familiarity with variables and core programming concepts will be helpful here. We will also make use of the Format and Sleep functions, and employ the DateDiff function to calculate differences between times. To display our results, I use a custom status box, which I have covered in other free videos available on my website and YouTube channel.
Let me set the scene with a real example from my own work. I have a customer form in my database that has grown more complex as I have added new features over the years. Among other things, every time I use the On Current event to navigate to a different customer, the database checks for unread emails, displays membership level, and verifies if there are any unpaid orders. These tasks are useful, but as the form evolved, I noticed that each switch to a new customer seemed to be getting slower – taking as long as four or five seconds. While that might not sound terrible, if you go through 100 records in a day, those seconds quickly add up. If you are running similar logic inside loops for thousands of records, tracking and reducing this time can save you a lot of frustration and improve efficiency.
Our goal today is to identify exactly where the slowdowns are occurring. In this lesson, I am going to demonstrate how to set up your code so you can analyze the running time of different parts of your events. We are going to simulate three different subroutines within a single button click and purposefully add delays using the Sleep function. This way, we can demonstrate how Timer helps us track their execution times.
Imagine you set up three simple subroutines: sub1, sub2, and sub3. Each one simulates a different database operation. For demonstration, let's use Sleep to artificially delay their execution time: sub1 waits 1.3 seconds, sub2 waits 2.5 seconds, and sub3 waits 0.2 seconds. The point is to observe which of these subs is slowing us down, without relying on guesswork.
In practical situations, you could try to use the Now function along with Date or DateDiff to measure elapsed time. However, the Now function is only accurate down to the nearest second. That is sufficient if your tasks are taking many seconds to run, but not if you need granular detail. When you are troubleshooting something that only takes a fraction of a second, you need a more precise measurement. This is where Timer shines.
To use Timer, declare your timing variables as Double (to store floating point numbers), since Timer returns the number of seconds (with decimal precision) since midnight. Each time you run a subroutine, capture the start time, let the routine execute, then capture the end time and subtract. This gives you an accurate picture of exactly how long each code block is taking – with results often accurate to within a few hundredths of a second.
When I implemented this in my own code, I was able to immediately see which subroutine was consuming the most time. From there, I could concentrate my optimization efforts only where they were really needed. For example, you might find an inefficient DCount function where you could use DLookup instead, or you could rewrite your logic altogether to speed things up. In my own database, doing this just recently cut a particular event's execution time in half.
Let's cover a few technical notes about using Timer. First, Timer is a reserved word, so do not use it as a table or field name. Timer returns the number of seconds since midnight, as a Single floating point value, typically between 0 and 86400. In practice, its resolution is about 1/64th of a second, which works out to roughly 15.6 milliseconds. While it is not quite down to individual milliseconds, it is close enough for almost any Access performance measurement you need.
A couple of caveats: If your measurement period might roll over midnight, you will need to handle the reset from 86400 to 0 yourself in the code – Timer will wrap back to zero once per day. This usually is not a problem unless your process could conceivably run that long.
Timer can be useful in a few other scenarios. For example, you can use it to measure idle time, or to control timing in custom code (although for short pauses, Sleep is usually appropriate). You could even use it in simple games you program within Access, or to monitor user interface delays in more advanced applications. However, for most tracking where accuracy more granular than one second is not necessary – like determining if a user has been idle for over a minute – standard date fields are usually more than adequate.
In summary, while Timer might not be a function you use every day, having it in your developer toolbox is important. I found it especially helpful when I had that nagging feeling that my forms were loading too slowly, and it allowed me to pinpoint exactly where the inefficiency was happening.
Looking ahead, I will be going into more advanced database optimization techniques in upcoming videos. Once you locate which code segments are slow, there are a lot of strategies for speeding things up, and we will cover those soon.
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 the Sleep function to simulate slow processes
Creating multiple subroutines for timing analysis
Measuring code execution time with date fields
Using DateDiff to calculate elapsed seconds
Switching from DateDiff to the Timer function
Tracking execution time with millisecond precision
Storing Timer values in Double variables
Displaying execution times with a status box
Identifying slow-running VBA subroutines
Simulating slow events in form buttons
Handling Timer rollover at midnight
Understanding limitations of Timer precision
Comparing Timer and the On Timer event
Best scenarios for using the Timer function in Access
Analyzing slow form or event performance
Pinpointing performance bottlenecks in Access VBA |
