Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Access VBA Timer Stops after a few hours

Posted on 2007-12-03
Medium Priority
Last Modified: 2013-11-27
I have a computer that has an app that has an on timer event. The computer is used for this only. The computer gets locked and the screen saver eventually comes on.
The timer continues to fire for up to about 3 hours... then it just stops.
When i saw that it stopped, i unlocked the PC, and the app was still open on the screen, but the timer wasn't running any longer. Once I closed the app, and re-opened, the timer started again and worked fine.
Any suggestions on why the timer would have stopped, and how I can prevent this from happening. I need the timer to continue running in 12 hour increments, even if the PC is locked and Screen Saver activated.
Question by:a260148
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 800 total points
ID: 20400046
check your Power options properties

right click on the desktop >properties
select the screen saver tab
click on monitor power
select  Power schemes
make sure that
       Turn off hard disks is set to  Never
       System stand By      Never

you can save this setting...

Assisted Solution

wizengamot earned 800 total points
ID: 20400051
I would actually code a timer object myself in this case.  Create a loop that loops once per second.  You can cause the loop to only loop once per second by calling the Threading.Thread.Sleep(1000).  This thread call works in .NET based apps so I would need to know what version of Access your running to locate the corresponding sleep call for you.  It might mean that you need to declare the sleep function directly as an WIN32 API call.   The code to do this is in the code block below....

Please note that 1000 milliseconds in this call is ~ approximately ~ 1 second.  If you need / want more precision than that, you may need to get a high precision ActiveX control (for earlier versions of access) or a corresponding .net control that provides the same service.

For vb6 (which creates activex objects) the article you want for high precision timers is

For .net there are three links:

Please note the InformIT link looks to me to have a huge amount of good info on this subject, but requires knowledge or the learning of some really cool API calls.

'put this at the top of the code file or in module declarations.
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
'example call
Sleep 1000

Open in new window


Author Comment

ID: 20400094
Ok, thanks for your replies...
As this is a machine that everyone uses... they don' t have admin rights on it... therefore I can't see the "Screen Saver" tab on the Properties menu. I did however check the "Powe Options Properties" under the Control Panel, and "Turn Off Hard Disks" and "system standby" are both set to Never. That was my first thought also... but apparently all PC's in our company automatically default to these options.

Not sure what you mean... when you say "I would actually code a timer object myself in this case.  Create a loop that loops once per second. " You talking about making a timer in some other app? I only have MS Access and VBA at my disposal, and using any VBA stops after a certain amount of time.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 20400526
Well, if VBA actually stops to function altogether that is something that I am not sure I can solve.  The way I read your answer was that the Timer Object stopped to function.  The timer is part of the control suite that you have access to in the toolbox.  

As far as coding my own timer, you can do this in VBA as well to the best of my knowledge.  I am not sure what version of access you are using but anything 2000 and up should be able to make a call to the WIN32 API.  This would be the same as doing a Win32 API call in any other language.  The URL: http://support.microsoft.com/kb/210219 does not relate directly to what your doing but it does show syntax for declaring the API and the code I put in my first post can be substituted and used in the same manner.  Then you create a loop like this:

'this would be an infinite loop, this is what you want.
Do Until True = False
    'code that compares your timer and executes your code when you want it to.
     'code to count the clock
    NumberOfSeconds = NumberOfSeconds + 1
    Sleep 1000

Author Comment

ID: 20400556
Thanks for your reply... So your saying instead of using the "On Timer" event at all... just having the code continuously loop. Makes sense, but i'm not if it is actually just the "On Timer" event that stops after some amount of time... or if its VBA just suppends all together.

I can't find anything on the web about VBA suspending... but that is what seems to be happening... after right about 3 hours.
LVL 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 400 total points
ID: 20401451
> The computer is used for this only.

OnTimer may work nicely. We had a machine running for years on Win2000 with a loop firing every minute. Your issue may be ram consumption due to what your loop performs.
Also, shut down the screen saver. These can be very aggressive. And as capricorn notes, remove all power saving settings (and turn of the monitor).

>  this is a machine that everyone uses

Now that is a new situation. If so everything may happen.
Get another machine for the users.


Author Comment

ID: 20403653
All, I re-started the app, and it has been running for 12 hours with not stopping of the Timer or VBA. Not sure why it stopped all the sudden on my first couple attempts? I didn't change any of the settings on the PC. I did however change a little of the Code. All i did different was instead of Dimming a RS, I used a Run SQL on timer. Not sure why or if this is what made the difference... but in any doubt, seems to be working fine now. Thanks to everyone for there input.
LVL 52

Expert Comment

by:Gustav Brock
ID: 20403745
> Not sure why or if this is what made the difference

You probably didn't clean up:

  Set rs = Nothing

All objects _must_ be set to Nothing before exiting the function or you eat up memory slowly but surely.


Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question