Executing a Program at a set time

[Liam1]

Hello,

I have an Access database that I would like to execute a function at a set time, i.e. 21:00hrs when the database is not busy.

At the moment I use a loop as follows,

dim dSubTime as time
'this is a user defined time

Do
Loop While dSubTime <> Time

Call fcnDoSomething

i.e. do absolutely nothing until the two times equal each other.

The issue is; this uses shed loads of memory & system resources.
Does anyone know a more efficient way?

Thanks,

Liam1

 

[Mute101]

Use a form timer and use the On Timer event to check once a minute for the appropriate time, then run your code.

----------------------------------------
Of all the things I have lost in my life, the thing I miss the MOST is my mind!
----------------------------------------

 

[p27br]

or you can use Windows Scheduled Tasks in System Utilities.

 

[CajunCenturion]

If the application will not be running, and when the appropriate time rolls around, fire up the app and execute the function, then I would follow p27br lead in looking into the Task Scheduler.

However, if the app is already running, and you do not want to wasted system resources by checking times every minute, then I would use the SetTimer API which is not bound by 65 second intervals. You set the Timer to fire when you want to execute the function and in fact tell it the name of the function to execute when it fires.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Liam1]

Cheers for the info,

Basically the application needs some user input, so will rely on the use (amoungst other things) setting the time they want it to execute, so yes it will be open, and I cannot use the scheduler.

My coding is poor
What is an API? All I know is an Advance Program Interface?

Thanks,

Liam1

 

[CajunCenturion]

API is Application Program Interface, and it is a method by which you have access to system functions.

In a code module, in the Declarations Sections at the top, make the following declarations:

Public Declare Function SetTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Public Declare Function KillTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long
Dim mLng_TimerID As Long

Then drop the following two functions into the module, replacing my time (14:00:00), with your desired kick off time. This example automatically calculates the number of seconds between now and 2:00 pm.

Public Sub ActivateTimer()

   Dim lLng_TimerInterval     As Long
   
   lLng_TimerInterval = DateDiff("s", Time(), "14:00:00") * 1000
   mLng_TimerID = SetTimer(0, 1, lLng_TimerInterval, AddressOf MyTimerHandler)
   
End Sub

Public Sub MyTimerHandler()

   KillTimer 0, mLng_TimerID
   MsgBox "Execute Your Report"
' Reset the Timer for 24 hours later
   mLng_TimerID = SetTimer(0, 1, 86400000, AddressOf MyTimerHandler)

End Sub

Call the ActivateTimer as soon as you know when you want to the process to start, and replace the MsgBox with the code for your function. In the example, the Timer is reset for 24 hours later, but you don't have to do that. You don't have to reset it, or you can reset it to start over at whatever time you desire. The parmater (86400000) is the number of milliseconds between now and the time you want to Timer to fire.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Liam1]

CajunCenturion,

I shall give it a whirl, Thanks for the help.

Liam1