Sleep
By Richard Rost
4 years ago
Sleep Function to Make Microsoft Access VBA Wait
In this Microsoft Access tutorial, I will teach you how to use the Sleep function to cause your database to stop and wait for a specific number of seconds. This is great for pausing your application for any number of reasons including multiple beeps, flashing text, slowing down email sending, and lots more.
Pre-Requisite
Recommended Course
Free Source Code
Links
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, #fasttips, sleep, wait, pause program execution, sleep api, sleep access vba, countdown, abort, flash, flashing, slow down, slowing down, sleepsec
Subscribe to Sleep
Get notifications when this page is updated
Intro
In this video, I will show you how to use the Sleep function in Microsoft Access with VBA to pause your database operations for a set amount of time. We will talk about different reasons why adding a delay can be useful, such as creating countdowns, spacing out multiple actions like beeps or email sends, and managing user interface timing. I will walk you through using the Windows API to add the Sleep function to your global module, explain how to work with milliseconds and longer pauses, and demonstrate practical examples like countdown timers and abort options for users.
Transcript
Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, I'm going to show you how to use the Sleep function to make your database wait in Microsoft Access. The Sleep function is very versatile. I use it all the time. It basically lets you have your VBA code execution pause and wait a certain number of milliseconds, or full seconds if you want, until something else happens.
Now, why would you want to do this? Isn't the whole point of faster computers so things process faster? You don't want to put pauses in. There are some reasons why, usually they are user interface-related. For example, if you want to have multiple beeps in a row, you can't just issue five beep commands because the loop will go real fast and you won't hear them all, so you can put a slight pause between them. I'll show you how in just a few minutes.
You can do the same thing with flashing colors. I've got another whole video where I show you how to have different text boxes flash yellow and black or whatever. I'll put a link to that video down below. You can click on it. You could give the user a countdown. There are a lot of reasons why you might want to give them a five-second countdown before shutting the database down, for example.
Sending emails, for example, give them a 30-second countdown after they hit send. Are you sure? You want to give them a chance to abort that. Or you might want to slow down processes. I used to have a printer years ago that if I sent too many jobs to the printer at once it would lock up and cause errors. So I used the Sleep function so it would pause. I had like 15 reports that had to print in a row, and so after each report it would wait like 30 seconds and then send the next one.
Same thing with email. If you are sending emails out, you might want to slow down email delivery. For example, I use Gmail and Gmail limits you to, I think, like three or four hundred emails per day. So if I want to send out a whole bunch of emails, I slow it down so that it only sends one email maybe every 20 seconds so I don't run into problems with Gmail.
There are lots of reasons why you might want to slow down your code. Sleep is a very versatile function. It's almost as good as the time Picard told Data to issue the sleep command to the Borg. Remember that? Sleep, Data. He's exhausted. Yes, Doctor. I mean, seriously, if it works on the Borg, it's going to work for your database.
Before we get started with the Sleep stuff, you need to know a little bit of VBA. If you haven't done any VBA programming, go watch this video. It'll teach you everything you need to know about VBA in about 20 minutes. It's not scary. Just follow along with me step by step. You'll be just fine. You learn a little tiny bit of VBA and you can make your database a whole lot more powerful.
Now, there's a function that we need for Sleep that's actually not part of Access. It's something called a Windows API. It's an application programming interface that lets you talk to Windows directly through this thing called kernel32. Do you have to worry about that or wonder what it is? No, don't worry about it. We're just going to copy and paste some code.
I'm going to let you into the Code Vault. Normally, the Code Vault is reserved for my Gold members, but I'm making Sleep available for everyone to give you a little taste of what's in the Code Vault. There's a whole ton of cool stuff in there.
See the URL right there? I'll put it down below in the description below the video. You can click on it to go to the Code Vault and grab this code right here. You can copy and paste it, or if you feel like typing it off the screen, go ahead, I don't care.
That link is going to take you here in the Code Vault. What you want is this code right here, everything from this Public Declare down to there. You're going to copy that to your clipboard, Control-C.
One little note though, if you're still running any versions of 32-bit Office, you have to replace this line here with this stuff down here. That is for 32-bit Office. That is for 64-bit Office. Since most people now are on 64-bit, I don't bother putting it up top here. This is all you need if you've got the latest version of Office. If you're running Microsoft 365 or whatever, which you should be by now, that's what you need right there. Copy to your clipboard.
Let's go over to Access. Here I am in the TechHelp free template. This is a free database. You can grab a copy of this from my website if you want to. You'll find links down below.
We've got that code copied in our clipboard. We need to put it in a public global module. We're going to go here, go to Create. Come over here, click on Module, not Class Module, Module. That will bring up our little friendly VBA window. Now, we're going to paste that code in. Boom, there you go. That's what it should look like, exactly like that.
I'm going to explain it all in just a minute. Let's save this first. Control-S, save that. I'm going to save this as my global module, global mod, whatever you want to call it. Usually, this global module will collect all the little bits and pieces of code that I need. They don't like all one big module.
What does this mean? Since Sleep isn't part of Access, we have to go out to Windows. There's a kernel32 file. You don't have to worry about what the name is; it's called kernel32. It's a library file. What this is saying is we're going to declare a sub in our database. We're going to name that sub Sleep, and we're going to get it from kernel32. That's basically what that means. It is more advanced than that, but you get the gist; you don't have to know everything about this to use it. Kind of like a car. You don't have to know how everything works under the hood to drive it.
Now, it takes one parameter. That parameter is a number of milliseconds. For one second, that would be 1,000 milliseconds.
We'll get to SleepSec in just a minute. First, let me show you what happens if we don't use Sleep to play multiple beeps. Come over here, Design View. We're going to use my little Hello World button here. We're going to rename this guy to 'Do Some Beeps.' We'll make it bigger. If you don't know how this Status Box thing works, go watch my Contact History Database or Contact History video. I'll put a link to that down below as well. I explain how I built the Status Box.
Go to Build Event here. This is my Hello World status. 'Status Hello World' just puts Hello World in that box. Here it is right here. StatusBox equals whatever's in there plus what I send to it.
Now, if I want to do some beeps, let's say instead of a status, I'm going to say Beep. What does Beep do? It just plays the PC speaker and makes your beep. Well, it used to be the PC speaker back... I'm dating myself now. There used to be an actual physical little speaker inside the PC case that was called the PC speaker. If you wanted multimedia sound, you had to buy a sound card and plug extra speakers into that. So Beep would just beep that little speaker.
Now, if you want to do multiple beeps in a row, watch what happens. If I come in here and go Beep, Beep, Beep, I'll say I want to give three beeps. Save it. Come back out here and go - see, it still sounds like one beep because they all kind of run into each other.
What you want to do is put a pause between them. That's what Sleep is for so the user will actually hear three beeps. Do Beep, then you go Sleep. Now, how long do you want to Sleep in milliseconds? A whole second might be too long. Let's go 500 milliseconds. That's basically half a second. Sleep 500. You can vary that. You can change it to 200, 800, make them different, whatever. Your database - you can put the Legos together however you want.
Save it. Come back out here. And now, did you hear that? You had three distinct beeps.
Along the same lines, let's say you want to give the user a five-second warning to shut down the database. So you click 'Shut Down the Database' and it counts down five seconds. After five, it gives them a chance to hit abort, and if they don't, it shuts the database down. How do we do that?
Go to Close Database, right-click, Build Event. In here, we're going to say Status - 'Closing Database.' Then we'll go Beep. Then status - '5', Beep, status - '4', and so on. Let me copy and paste. Beep, copy, do another Beep, 3, 2, 1. Then right here, it'll exit the database, which you can just put Quit to close the database, but I'm not going to actually do that for class, so I'll leave that comment down there. You can see that's where we actually would do the Quit or whatever you want to fire off. Then we'll change this to 3, 2, 1 and then status - 'Bye Bye.'
Again, if you have it like this, save it, back out here, do it, click, it just goes very fast. So that's where our Sleep comes into play. You give them five seconds. Now, I don't want to keep adding Sleep, Sleep, Sleep. This is getting long and it's very repetitive.
We can use a For...Next loop for this. If you've never done a For...Next loop before, you're in for a treat. I love For...Next loops. I use them all the time. I think I use While loops more, but For...Next loops are one of my top two favorite loops.
So go watch this if you've never done a For...Next loop, but I'm going to show you how in just a second. We need a variable so we can do some counting. Dim x As Long. Right here, I'm going to say For x = 1 To 5. Then we're going to say Beep and then Status, but instead of statusing a number, I want to status x. Then I can get rid of all this extra copy and paste. Next right here.
Now in this loop is where I can put the Sleep. Maybe we'll go a full second. When that loop is all done, 'Bye Bye.'
Ready? Save it, come back out and hit the button. Look at that, isn't that cute? And then 'Bye Bye,' it exits.
Now we've got to give them a way in here to quit if they don't want to. Design View. Come in here and then down here on the bottom. I did this in the last video too, but we'll do it again, we'll grab a little check box. Where is it? Right there. I can never find the check box. I'm used to looking for that old icon. Still, okay, down here we'll put "Abort" like that.
In the video I just did, yesterday's video - the coin toss video - I show you how to hide this guy if you don't need it. We're not going to get into that one. If you want to see how to do that, watch the coin toss video. I'll put a link to that as well. We'll call this guy 'Abort.'
What I'll do is inside this loop, I'm going to say up here before we start, 'Abort = False.' Then inside this loop, I'm going to say 'If Abort Then Exit For.' In other words, get out of dodge. Down here, we're going to say 'If Abort Then Status "Shutdown Self-Destruct Canceled" Else "Bye Bye."' This is where you put your Quit.
So if we exit the loop, if the user clicks Abort, we're going to exit the loop. If down here they've checked Abort, we're going to cancel the self-destruct. Otherwise, we're going to exit the database.
Ready? Save it. Close this. Here we go. Click. I'm going to let it just go, then I'm going to click Abort. There we go, bye bye. Let's do it again. Click, this time I'll click Abort. Wait - that's happening. There it goes. Takes a second.
That's one of the reasons why I made the SleepSec function. Remember I said we'd get to that in a minute? Let's go back to the module here.
What SleepSec does is, if you want a longer pause than one second, you send SleepSec a bigger number. Let's say you want a 30-second pause. If you're pausing that long, to the user it will look like nothing is happening. If they click, it won't register. Why? Because the cycle is locked. Sleep will lock up the system as much. If I come in here, go back to the main menu real quick, and let's say there is a five-second pause between each one of those. Save it. Now if I click, see it's much longer between those. If I click now, I just clicked and nothing appears to happen for a good while until finally it registers that check, and then, at the end of the next cycle, it aborts.
What SleepSec does is, no matter how many seconds you want to abort for, there's an internal loop inside here that only pauses a second at a time. You could say Sleep 30 seconds and it'll come in here and it'll loop 30 times, sleeping one second at a time. You can change this. You can make this 100 milliseconds and multiply that by 10 if you want to. There are a lot of different things you can do. I think one second is enough for people waiting for a click to register.
If I still want that five-second pause, instead of saying Sleep 5000, I'm going to say SleepSec 5. Then there's the allow events. You can not allow events if you don't want the user to be able to abort it, but usually that's true, so I just leave that alone.
Save it and come back out here. Same five-second wait. Click, then wait five seconds. If I click now, all right, that check box checked almost immediately. So at least it lets you see that something is happening every second. You've got a two-minute pause in here.
Sleep is a great way to have your execution pause. Like I said, if you are sending multiple emails, you want to give someone a countdown or any kind of thing.
I'll give you some links to some other videos. Here's one that shows you how to use the Timer event to have some flashing text. The Timer event works similar to Sleep, but it is built into Access and it runs at the form level. So the whole form itself has a timer that runs. You can set what that is.
In my Access Developer 18 class, I show you how to use the Sleep timer to slow down a progress bar if you actually want it to slow down. There are a lot of reasons why. If you don't want something running too fast...
I'm old, so back in the 90s, when games were written for XT or PC, you know, slower systems, and then the 386 and 486s came out, they actually had a turbo button you'd have to click on to slow the processor down. Otherwise, your games would run way too fast because they weren't based on a system clock, they were based on how fast the computer could process.
Same thing here, you might want to slow down the progress of a particular event, like I said before, sending emails or whatever.
In Developer 18, I talk about that stuff. That little notification pops up. That's why I usually turn those things off when recording class, to stop notifications.
There's another video I did that's got a form Timer event similar to Sleep, but this one gives you a reminder pop-up. So if you get stuff like new sales leads, it'll count down like 10 minutes, whatever, then check for stuff, then give you a pop-up message.
All kinds of different things you can do with timer events and Sleep.
So there you go. That's how you use the Sleep function. Slow down your stuff. Make people know that stuff's happening. Hope you learned something. Hope you had some fun. We'll see you next time.
Oh, and don't forget. I am Locutus, a Borg.
Quiz
Q1. What is the main purpose of the Sleep function in Microsoft Access VBA?
A. To pause code execution for a specified amount of time
B. To clear all variables in the database
C. To permanently stop code from running
D. To change user permissions
Q2. Why would you want to use the Sleep function in your Access database?
A. To make computers run faster
B. For user interface enhancements like countdowns or pauses between actions
C. To delete records automatically
D. To permanently close the database
Q3. Which unit of time does the Sleep function use as its parameter?
A. Seconds
B. Minutes
C. Milliseconds
D. Microseconds
Q4. Which file does the Sleep function access through Windows API?
A. win32.dll
B. kernel32
C. sleepapi.sys
D. accessmod.vba
Q5. How do you add the Sleep function to your Access database?
A. By installing an Access add-in
B. By copying a code snippet from the Code Vault into a VBA global module
C. By upgrading to Access Developer Edition
D. By enabling it in the Access options menu
Q6. What is typically required before using the Sleep function in Access VBA?
A. Knowledge of SQL queries
B. Basic understanding of VBA and how to create a module
C. Setting up ODBC drivers
D. Installing Visual Studio
Q7. What is the major difference between versions of Office when using the Sleep declaration?
A. 32-bit Office requires a different declaration than 64-bit Office
B. Office 2010 does not support Sleep at all
C. Only 64-bit systems can use Sleep
D. The difference is only in the interface, not the code
Q8. What does putting the Sleep function inside a loop allow you to do?
A. Play multiple sounds simultaneously
B. Add incremental delays, such as countdowns or repeated actions with pauses
C. Increase the speed of code execution
D. Skip code execution entirely
Q9. Why might you use a For...Next loop in combination with the Sleep function?
A. To quickly process multiple records
B. To create a countdown timer that gives users time to react
C. To exit Access more quickly
D. To bypass error messages
Q10. What is the purpose of the Abort check box demonstrated in the tutorial?
A. To immediately close Access when checked
B. To give the user the option to cancel the countdown during a loop
C. To reset all records
D. To restart the Sleep timer
Q11. What advantage does the custom SleepSec function provide compared to Sleep?
A. It only works in 32-bit Access
B. It allows longer pauses while checking for user input more frequently
C. It allows code to execute faster
D. It plays a sound during the wait
Q12. What is a potential problem with using a long Sleep directly (such as Sleep 30000) without breaking it up?
A. The database will auto-close
B. User input is blocked during the entire pause
C. The code will run twice as fast
D. It deletes records unintentionally
Q13. What other timing-related function is mentioned as being built into Access forms?
A. SleepNow
B. Timer event
C. WaitCommand
D. AccessPause
Q14. Why might you want to slow down batch processes like printing reports or sending emails using Sleep?
A. To make your computer sound like it's working harder
B. To avoid overloading hardware or hitting provider limits
C. To introduce visual effects
D. To reduce the need for user interaction
Q15. What should you do if you want to allow users to cancel a pause or loop executed with Sleep?
A. Use a Timer event instead
B. Regularly check for user action (like an Abort check box) during the pause
C. Set Sleep duration higher
D. Remove all message boxes from the form
Answers: 1-A; 2-B; 3-C; 4-B; 5-B; 6-B; 7-A; 8-B; 9-B; 10-B; 11-B; 12-B; 13-B; 14-B; 15-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 video from Access Learning Zone covers how to use the Sleep function in Microsoft Access to create pauses or delays within your VBA code. The Sleep function is quite flexible and is something I find myself using regularly. It allows you to halt code execution for a set period, measured in milliseconds or seconds, until a particular action occurs.
You might be asking why you would want to slow down your database when computers are optimized for speed. However, there are valid use cases, especially regarding user interface interactions. For example, if you want to trigger multiple beeps consecutively, simply looping the Beep command will happen too quickly for the user to distinguish between the sounds. By incorporating a brief pause with Sleep between each beep, you provide the spacing that's needed.
This same principle applies if you want to flash colors on your form, provide a countdown to users, or introduce a delay before taking an action, like shutting down the database or sending an email. I once had a printer that would jam if sent too many reports quickly, so I used the Sleep function to insert a pause between print jobs. Similarly, if you're sending mass emails through a service like Gmail, which has daily send limits, slowing down the send rate using Sleep helps you avoid issues with those limitations.
The Sleep function is called via the Windows API rather than being a native Access function. This API lets your Access application communicate directly with Windows, specifically using the kernel32 library. However, you do not need to worry about the intricate details of how Windows APIs work or what kernel32 is. I provide the necessary code that you can simply copy and paste into your project.
I'm also making the Sleep function code freely available in my Code Vault, which usually is reserved for members. Just visit the link in the description to access the code. It covers both 64-bit and 32-bit versions of Office, but since most users are on the latest 64-bit Office now, you'll likely only need the top version of the code.
Once you have the code, you should insert it into a public global module in your database. Access this by creating a new regular Module (not a Class Module) in your VBA editor, then pasting the code you copied. Save the module with a logical name, such as "global mod," which you can use to store other reusable code snippets as needed.
The Sleep function accepts one parameter: the number of milliseconds to pause. For example, 1,000 milliseconds equals one second.
To illustrate how this works, imagine you want to play three beeps in a row. If you simply issue three consecutive Beep commands, they run so quickly that you only hear one sound. Instead, after each Beep insert a Sleep command with, for example, 500 milliseconds. This provides the required delay, making each beep distinct and audible.
Another common use is creating countdown timers. Suppose you want to warn users that the database is about to close. By placing the Sleep function within a loop, you can show a countdown (e.g., from five down to one), beeping at each step, and only perform the final shutdown at the end. If you want to give users the ability to abort, you can add a checkbox labeled Abort. By checking this within your countdown loop, you can exit the process early if the user decides to cancel.
One issue when using the Sleep function for longer delays is that it temporarily locks up the database interface during the pause, so it may seem unresponsive if you are waiting many seconds. To address this, I created a wrapper function called SleepSec. This function allows you to pause for multiple seconds, breaking the wait into one-second intervals so that UI events like clicking the Abort checkbox can still register each second. For instance, if you want a five-second pause, you call SleepSec 5. If a user clicks Abort, the check is detected at the end of each one-second interval, ensuring better responsiveness.
There are many other uses for Sleep and similar timer-based techniques. The Timer event is another feature built into Access that allows you to run code at timed intervals on a form. For example, you might use it to flash text, display reminders, or periodically check for new data like sales leads. I also teach how to leverage the Sleep function to control the speed of progress bars in one of my advanced Developer classes.
These approaches can be helpful for a wide variety of database tasks where controlling the pace of execution or providing user feedback is necessary. Whether you're providing alerts, slowing down output, or managing complicated processes, Sleep and timer events can make your applications more user-friendly and robust.
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 pause VBA code in Access
Copying and pasting Windows API Sleep code into a module
Differences between 32-bit and 64-bit Sleep declarations
Creating a global module to store Sleep code
Understanding kernel32 and Windows API declarations
Pausing execution in a VBA loop using Sleep
Adding audible beeps with pauses in Access VBA
Implementing a countdown with message updates and Sleep
Using a For...Next loop for countdown timers
Allowing user abort during countdown with a checkbox
Exiting loops early in VBA when Abort is checked
Developing the SleepSec function for longer, interruptible pauses
Difference between Sleep and SleepSec in user interface responsiveness
Using VBA Sleep to slow processes like printing or sending emails
Handling user events during pauses with SleepSec
Refining countdown and shutdown sequences with user input
|
