How to Use Sleep to Pause VBA in MS Access

One thing that most developers run into from time-to-time is how to pause their code for a brief moment. It could be because some other process needs some time to finish, or you just don’t want something to go as fast as the computer can do it. While the Sleep function/sub is not directly available in VBA, you can use it by declaring the function and referencing a Windows dll. Once you’re set up, you can use the sleep function throughout your application.
Related Videos:
How to Use a File Dialog in MS Access to Allow Users to Pick a File or Folder
• How to Use a File Dial...
How to Write a Custom Text File Using MS Access VBA
• How to Write a Custom ...
How to Integrate Machine Learning into Microsoft Access Applications
• How to Integrate Machi...
How to Use Base64 in Microsoft Access
• How to Use Base64 in M...
How to Use Sleep to Pause VBA in MS Access
You are watching this video now!
How to Open Excel from Access Using VBA
• How to Open Excel from...
How to Use vbScript to Open and Work with Data in Access accdb Files - Late Binding Example
• How to Use vbScript to...
Want to work with me 1:1? Contact me today and book your free 20 min consultation!
Contact form you can find at www.mackenziemackenzie.com/
Follow me on social media:
/ mackenziedataanalytics
/ seamacke
/ seamacke
/ seamacke
/ psmackenzie
Get Microsoft Office including Access:
click.linksynergy.com/fs-bin/...
Got a TH-cam Channel? I use TubeBuddy, it is awesome. Give it a try:
www.tubebuddy.com/seanmackenz...
#MSAccessSleepFunction #MSAccess #VBA
• How to Use Sleep to Pa...

[@stephanies6316]

Thank you for sharing .... this solved my problem

[@seanmackenziedataengineering]

You're welcome! Glad it helped, cheers

[@janezklun]

Good one, thank you for sharing

[@seanmackenziedataengineering]

Glad it helped!

[@kassemaubaid9217]

hello Sir MacKenzie.. I dont know for what we use sleep function

[@seanmackenziedataengineering]

Sometimes, you want to pause your code. Maybe you need to wait for some other external process to finish before your code can continue. Maybe your code triggers another process at the start of execution, then checks the result of that process at the end before you can continue. You know the process always takes 3 seconds to complete. So, you can start your code (which triggers the process) then sleep for 4 seconds before it consumes data from the external process (simple example and it assumes the process always takes 3 seconds).

[@robertzimmerman8910]

I get errors right off the bat since I'm on Windows 10 64 bit and the kernel32.dll declaration blows up with a cryptic error message about LongPtr and 64 bit declarations

[@seanmackenziedataengineering]

For 64-bit installations, you can try using:
Private Declare PtrSafe Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
Note PtrSafe added to the beginning of that line. It is for declarations in 64-bit Office installations.

[@ValmisFilm]

how can I use Sleep over the whole workbook? At this point, I must add this declaration to each module.

[@seanmackenziedataengineering]

Instead of Private Declare, try using Public Declare. Then you can call it from anywhere. You will need to delete the declaration from all other modules. Great question!

[@ValmisFilm]

@@seanmackenziedataengineering Thank you! Like "Public Sub" - I just understood that all these years I did this one wrong :D

[@kamleshjain6872]

Nice... useful when you want user to wait...

[@seanmackenziedataengineering]

Definitely!

[@tigerdataimarticus7088]

If multiple user click on same time to export data to common database, is this code userful then? Becoz I am facing error 😭 I have asked to u same query earlier. Please help

[@seanmackenziedataengineering]

Good question! No, this code won't help in that situation, because each user instance will run separately, so the pause will only affect their computer. In your case, I would suggest creating a queue in your app to handle this concurrency issue.

[@tigerdataimarticus7088]

@@seanmackenziedataengineering how, please help SOS 🙏

[@seanmackenziedataengineering]

@@tigerdataimarticus7088 What kind of error do you receive?

[@tigerdataimarticus7088]

@@seanmackenziedataengineering error description: your network access was interrupted. To continue, close the database, and then open it again. Error Number: 3043

[@seanmackenziedataengineering]

@@tigerdataimarticus7088 I've seen this many times at customer sites. It is usually a network issue, meaning that the connection to the backend is interrupted. First try to make sure all user network connections are solid, no pinched network cables, no super slow wireless connections etc.
The silver bullet to fix this problem is to convert your backend to SQL Express or Azure SQL, as managed data will be faster, handle connectivity issues easily, and will generally be more robust.
I had one client with this exact problem for 25 users on a heavy load Access db across warehouses and offices. This error was common and was fixed immediately when converting to Express on the backend. Almost no changes to the front end code except to change the table links to ODBC.
You can still try a queue for your big data operation (as we mentioned before); but first sort out if there is a network problem. Usually it will be one of the users with a bad connection.