schedule a query to run every night 1

[1angryadmin]

ok i have a database.

i have a query written which i would like to run every night at a certain time.

can anyone shed light on the best way to accomplish this?

Please end my suffering...

 

[cghoga]

do you have access to a scheduler?

you could put your app (or a batch file that opens the app)on the scheduler to run nightly.
then, program your query to run when the database opens
and at the end of your proc close the mdb.

you could also create an On Timer event within a form to kick off at a certain time and execute your query. however, to do this you would need to leave your PC up all night.

hope this helps you.

 

[1angryadmin]

the access database that i speak of is sitting on our network file server

the file server is a windows 2000 server

it is on 24/7/365

 

[CajunCenturion]

I would consider using the SetTimer API to trigger an event in the program to fire off at the appropriate time.

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

 

[1angryadmin]

Set timer API

is this vbscript?

 

[CajunCenturion]

No, it's not VBScript, it's an API call.

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

 

[1angryadmin]

could you give me an example

the database name is test.mdb

the query i would like to run is qryupdate_total

 

[1angryadmin]

ok i have the database setup in the scheduler but i can't figure out how to make the query run automatically when the database opens

 

[willir]

1angryadmin

A totally different approach would be to use the timer event on a form. Keep the database open, and the form loaded, and setup the logic to kick off query every 24 hrs.

If you are not happy about using every 24 hrs type of thing, you could have the timer event check the time every 5, 10 or 15 min and kick off at your designated time.

Richard

 

[CajunCenturion]

In a module, in the Declarations Sections, 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.

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[/code]
mLng_TimerID = SetTimer(0, 1, 86400000, AddressOf MyTimerHandler)


End Sub
[/code]
Call the ActivateTimer in the when in the start up of your program, and also be sure to kill the timer on app shutdown


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

 

[cghoga]

create a form
in its On Open event proc enter your code
save and exit the form
in the Tools - Startup - Display Form/Page specify your form

hope this helps.

 

[cghoga]

create a form

in its On Open event proc enter your code

DoCmd.OpenQuery "qryupdate_total"

save and exit the form

in the Tools - Startup - Display Form/Page specify your form

 

[1angryadmin]

awesome cghoga now one last thing how do i get it to close the database when its done running the query...


btw i love this stuff...hehehe

 

[1angryadmin]

also when i run the form its an update query so access gives me 2 arguments

1. you are about to run an update query

2. you are about to affect x number of rows

 

[1angryadmin]

maybe by using DoCmd.SetWarnings false ??

 

[1angryadmin]

ok i am using the following as code in a the form that automatically opens when the database opens

Private Sub Form_Load()
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryUpdateSqFt"
End Sub

all i need to know now is what code to add to this to close the database when its done running the query

 

[cghoga]

sorry not to get back to you sooner.

DoCmd.SetWarnings False will suppress the messages from popping up. To turn them back on it's simply DoCmd.SetWarnings True.

To exit the database I think a Quit will do.

So, I think your code should look like this...

Private Sub Form_Load()
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryUpdateSqFt"
DoCmd.SetWarnings True
Quit
End Sub

hope this helps.

 

[1angryadmin]

ok final post on this issue unless one of you has something to add

below is the code that i have in the form

the form is in a database which is scheduled to run every night at 10pm

it runs the query automatically without confirmation and then closes itself

Private Sub Form_Load()
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryUpdateSqFt"
DoCmd.Quit
End Sub

tha tha tha thats all folks...thanks everyone...