timer question

[ave]

I would like to have access open and run a specified query at 11:00 PM each
night

How would i write the timer code to do this?

thanks in advance

 

[Ron Weiner]

Ave

I won't write the code for ya, but I am happy to give you a little direction
to get this done.

Create a Table (tblRunQuery) with one Column (NextRun) as a Date/Time field.
Open the table and in the New Row enter a date like 5/12/2004 11:00Pm
Close the table
Create a new form (frmRunQuery)
In the Forms TimerInterval Property enter 60000
Create an Event Procedure for the InTimer Property
Next you need to write some VBA for the Event Property. The Code it Might
look like:
Private Sub Form_Timer()
Dim dteNextRun as Date, strSql as String
dteNextRun = Dlookup("NextRun","tblRunNext")
if Now() > dteNextRun then
' Your code to run the query
' yada-yada-yada
' Next we need some code to re-Schedule our NextRun to same time
tomorrow.
strSql = "Update tblRunQuery Set NextRun = DateAdd('d',1,[NextRun])"
docmd.SetWarnings false
docmd.RunSql strSql
docmd.SetWarnings true
end if
end sub

Basically what you have created is a form with No interface, that once a
minute looks to see if it is time to run the query. If it isn't time yet it
does nothing. When the time has come to run the query it does so and then
sets the NextRun to one day after the current value. So if you set the
initial value to 5/12/2004 11:00pm it will reset the NextRun time to
5/13/2004 11:00pm when it executes.

Now that said this is just a skeleton of what you need to do as this routing
has NO ERROR Trapping at all. You also may want to create a log of the
Query start and stop times and any other events you are interested in so you
can have a history that the job did in fact run. Anyway there's my 2 cents,
don't spend it all in one place.

Ron W

 

[Mark C]

Wouldn't it be easier to have the code run on form open.
Set the form as the startup form of the project.
Have it check the time just to be on the safe side

Create a sheduled job to open the file at the correct time, with the
correct repetition.

Alternatively have the form check a startup parameter to make sure it is
running from the scheduled job, so you can access it easily enough to
modify and maintain the program.

mark

Ave

I won't write the code for ya, but I am happy to give you a little
direction to get this done.

Create a Table (tblRunQuery) with one Column (NextRun) as a Date/Time
field. Open the table and in the New Row enter a date like 5/12/2004
11:00Pm Close the table
Create a new form (frmRunQuery)
In the Forms TimerInterval Property enter 60000
Create an Event Procedure for the InTimer Property
Next you need to write some VBA for the Event Property. The Code it
Might look like:
Private Sub Form_Timer()
Dim dteNextRun as Date, strSql as String
dteNextRun = Dlookup("NextRun","tblRunNext")
if Now() > dteNextRun then
' Your code to run the query
' yada-yada-yada
' Next we need some code to re-Schedule our NextRun to same
time
tomorrow.
strSql = "Update tblRunQuery Set NextRun =
DateAdd('d',1,[NextRun])" docmd.SetWarnings false
docmd.RunSql strSql
docmd.SetWarnings true
end if
end sub

Basically what you have created is a form with No interface, that once
a minute looks to see if it is time to run the query. If it isn't
time yet it does nothing. When the time has come to run the query it
does so and then sets the NextRun to one day after the current value.
So if you set the initial value to 5/12/2004 11:00pm it will reset the
NextRun time to 5/13/2004 11:00pm when it executes.

Now that said this is just a skeleton of what you need to do as this
routing has NO ERROR Trapping at all. You also may want to create a
log of the Query start and stop times and any other events you are
interested in so you can have a history that the job did in fact run.
Anyway there's my 2 cents, don't spend it all in one place.

Ron W

I would like to have access open and run a specified query at 11:00
PM each
night

How would i write the timer code to do this?

thanks in advance

.................................................................
Posted via TITANnews - Uncensored Newsgroups Access-=Every Newsgroup - Anonymous, UNCENSORED, BROADBAND Downloads=-