[Webinar] Streamline your web hosting managementRegister Today


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
  • 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.
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.


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 53

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 53

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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

591 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