Run Macro Every 24 Hours On Timer

[fenhow]

Hi,

I have searched but am not having a lot of luck, can someone please direct me in the following area?

I have a db that is always open, I want a specific macro to run every 24 hours.

I am thinking the ontimer event but cannot seem to get it.

I am not sure what number to put in that represents 24 hours and the code to trigger it every 24 hours..

Can someone help?

Thanks in advance..

Fen How

 

[boblarson]

24 hours would be

1,000 * 60 = number of milliseconds in a minute
60 * number of milliseconds in a minute = number of minutes in an hour
24 * number of minutes in an hour = number of hours in 24 hour day

So, the total is 86,400,000

 

[KenHigg]

24 hours would be

1,000 * 60 = number of milliseconds in a minute
60 * number of milliseconds in a minute = number of minutes in an hour
24 * number of minutes in an hour = number of hours in 24 hour day

So, the total is 86,400,000

1,000 * 60 = number of milliseconds in a minute
60 * number of milliseconds in a minute = number of milliseconds in an hour
24 * number of milliseconds in an hour = number of milliseconds in 24 hour day


?!?

 

[georgedwilkinson]

Did you want to kick this baby off at a certain time of day or just 24 hours after the form first loads and every 24 hours after that?

 

[fenhow]

Thanks that got it,

I note that the timer event starts counting from the time the form or whatever is opened.

I want to run this macro every day at a specific time and sometimes the database gets shut down or updated.

Rather than having to execute the form at a specific time is there a way to code the application to run the macro say at 1200 midnight every day? regardless of when the database was last opened, closed or updated?

Thanks.

Fen How

 

[fenhow]

Sorry George,

I want to kick it off every day at say midnight...

Fen

 

[Banana]

What I would do (cavaet: I don't want to do anything with timer function- too buggy and cumbersome; would rather do something else but that's your decision) is:

When the database loads, I make sure that a startup form has been specified, and in OnLoad event of this form, hide that form then get the system time, calculate the time until midnight then set the timer that seconds. When the timer event goes off, do what you need, then insert in that number boblarson gave you.

 

[Pat Hartman]

I also have been bitten by timer events, not to mention the flicker they cause.

I use the windows scheduler to open the database at midnight and run the macro. To do this create a .bat file so you can use the /x argument to run the specific macro. It is preferable to schedule this on a server but if your IT folks won't allow that, then do it on a departmental PC. The advantage is that you don't have to leave the database open all the time.

 

[fenhow]

Thanks everyone how abou this?

I found this code lastnight but cannot seem to get it to work can one of you clear it up for me?

Thanks..

* Create a form, add a timer event to the form.
* Autoexec macro (on open DB macro).
Add the following commands:
- Open the timer form.
- Hide the Timer form (So, users
Cannot see interfere with it).
* On the timer form, Auto set the "On timer" event to activate when the form loads.

Private Sub Form_Load()
Form.TimerInterval = 1000 ‘ 1000 being every 1 second, the timer will run.
Me.Visible = False
End Sub

Add 2 fields to the Form -
1. - Being the = system time
2. Being the Targettime.... (Which is the time you want to kick everybody out)

Add your own variation of code, here’s an example:
(This code will run every 1000, or every 1 second…. Until it finds a match)
Public Sub Form_Timer()
Dim FrmDate As Date
Dim Sysdate As Date
' Set variable values
FrmDate = Me.txttime.Value
Sysdate = Format(Now, "HH:MM:SS")
' When FrmDate = the system clock, run required program.
If FrmDate = Sysdate Then
Docmd.quit
End If
End Sub

You can change the Target time to any time you wish…..
Just make sure you keep the form open at all times.
When the system date = the Target date, because everybody has this form open in the background (Hidden), Everybody will be kicked out of the database.

You could use the same principle for sending a 10 minute warning ….

 

[doco]

'   Create a form, add a timer event to the form.
'   Autoexec macro (on open DB macro).
'   Add the following commands: 
'       - Open the timer form.
'       - Hide the Timer form (So, users 
'           Cannot see interfere with it).
'   On the timer form, Auto set the "On timer" event 
'   to activate when the form loads.
'
Private Sub Form_Load()
 
' 1000 being every 1 second, the timer will run.
    Form.TimerInterval = 1000  
    Me.Visible = False
 
End Sub
'
'   Add 2 fields to the Form -
'       1. - Being the = system time
'       2. Being the Targettime.... (Which is the time you want to kick 
'                                        everybody out)
'
'   Add your own variation of code, here’s an example:
'   (This code will run every 1000, or every 1 second…. 
'                                      Until it finds a match)
Public Sub Form_Timer()
    Dim FrmDate As Date 
    Dim Sysdate As Date
 
'   Set variable values
'   [B]I think this is the problem area - you need to delimit date values[/B]
    FrmDate = "#" & Me.txttime.Value & "#"
    Sysdate = "#" & Format(Now, "HH:MM:SS") & "#"
 
'   When FrmDate = the system clock, run required program.
    If FrmDate = Sysdate Then
        Docmd.quit
    End If
 
End Sub

 

[Pat Hartman]

Do what you want but I strongly suggest that you stay away from timer events.

 

[burrcm]

Better to create a shortcut to the macro (right click - create shortcut) and use Windows Scheduler to run it. Db can be closed or open, doesn't matter, but if you want to close it again, do so with the macro, don't let scheduler terminate it or you will get errors.

Chris B

p.s. Pat, you don't need a batch file.

 

[fenhow]

This works but. The macro runs every second... I do not want to quit the DB so how can I only have the Macro run once..

Thanks.

Private Sub Form_Load()

' 1000 being every 1 second, the timer will run.
Form.TimerInterval = 1000
Me.Visible = False

End Sub
'
' Add 2 fields to the Form -
' 1. - Being the = system time
' 2. Being the Targettime.... (Which is the time you want to kick
' everybody out)
'
' Add your own variation of code, here’s an example:
' (This code will run every 1000, or every 1 second….
' Until it finds a match)
Public Sub Form_Timer()
Dim FrmDate As Date
Dim Sysdate As Date

' Set variable values
' I think this is the problem area - you need to delimit date values
FrmDate = "#" & Me.txttime.Value & "#"
Sysdate = "#" & Format(Now, "HH:MM:SS") & "#"

' When FrmDate = the system clock, run required program.
If FrmDate = Sysdate Then
Docmd.quit
End If

End Sub

 

[Pat Hartman]

If you are using Windows Scheduler, you can remove the timer event and just run the code you want to run.

 

[KenHigg]

Pat, Can you elaborate on how you go about implementing the Windows Scheduler? Can I do it on a workstation that I leave on or does it have to run off of a server?

 

[KenHigg]

Edit: Can it be used to force users out of a db if it needs to be brought down for maint. or is the timer still the best way to do this?

 

[Banana]

KenHigg,

Never tried, but that gave me the idea- I could use Windows Scheduler to run database, with the command prompt (/x, IIRC) stipulating that the database is to peform a certain action as a privileged user, which it then can kick out other users and do what it needs to. Can't see why it can't be done that way.

 

[KenHigg]

I'm not sure there's any other way to force a user out other than with a timer event - ?