How to Trigger MS Access Code based on specific ti

问题:

I have been trying to pull this off and maybe I am getting close. I have some code in Access I need to Run at a specific time of day. I do not want to use Windows Task Scheduler.

So I have this function:

Public Function RunProgram()
Dim RunDateFormat As String

RunDateFormat = Format(Now(), "HH:MM:SS")
    If RunDateFormat = ("15:30:00") Then

'Call these codes and other stuff in here

End If

Then I create a Macro called Macro1 to call the function, the form On Timer calls the Macro as indicated below.

The problem is if I set the interval to; example: 1000, I get an email every second and the program keeps running.

Please help: I need to set this so the code runs at the exact time but only one time.

回答1:

How about you use a static date variable:

static dateRan as date
if dateRan = Date then exit Function
If RunDateFormat = ("15:30:00") Then
dateRan = date
'Call these codes and other stuff in here
End If

This way you could keep it running overnight and it will work the next day assuming no error occurs to halt operation.

As I'm sure you're aware, doing a scheduled task through Access requires the form to be up and running so you have a fairly large opportunity to fail to run the your time sensitive task. I really do recommend using the task scheduler if it is feasible.

As hinted at by HelloW, I wouldn't go with every second for checking unless it is critical that the code run about the right time. If it is critical, you may want to do some checks and modify the timerinterval programmatically.

Like so:

If Hour(Now) < 14 or Hour(Now) >= 16 then
    me.TimerInterval = 3600000
else
    me.TimerInterval = 30000
end if

You could narrow it down more, to where you have the checks run more often the closer you get to the time you need to run your time sensitive code.

回答2:

Do the time checking in the form's timer event procedure.

Private Sub Form_Timer()
    If TimeValue(Now()) >= #15:30:00# Then
        ' run your job, then turn off the timer
        Me.TimerInterval = 0
    End If
End Sub

An approached based on the above code should allow you to avoid sending an email each time you do your time check.

If yours is not a single user application, this situation may be more complicated. If 2 or more users have the form open at 3:30 PM, should they all attempt to run the job? Can they? OTOH, if the job is user-specific, that may be what you want.

回答3:

Change Format(Now(), "HH:MM:00") to Format(Now(), "HH:MM:SS") This will give you the seconds which is what you seem to be looking for. As a best practice, I don't think it is a good idea to run this every second. Maybe every minute or quarter hour and test it then.