Solved

Access VBA Timer Stops after a few hours

Posted on 2007-12-03
8
1,645 Views
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.
Thanks
a260148
0
Comment
Question by:a260148
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 200 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...
0
 
LVL 1

Assisted Solution

by:wizengamot
wizengamot earned 200 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
http://www.vbaccelerator.com/home/vb/code/Libraries/HiResTimer/article.asp

For .net there are three links:
http://www.codeproject.com/KB/cs/highperformancetimercshar.aspx
and
http://www.informit.com/guides/content.aspx?g=dotnet&seqNum=242&rl=1
and
http://www.codeproject.com/KB/vb/vbnetstopwatch.aspx

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

0
 

Author Comment

by:a260148
ID: 20400094
Ok, thanks for your replies...
capricorn1:
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.

wizengamot:
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.
0
 
LVL 1

Expert Comment

by:wizengamot
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
Loop
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:a260148
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.
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 100 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.

/gustav
0
 

Author Comment

by:a260148
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.
0
 
LVL 49

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.

/gustav
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now