1

I’d like to be able to create a timer that would allow me to run a query at the same time each day. Does anyone know how to do this, please?

2

Hi All

In VBA there is a Timer function is available. Y cant u try that.

I hope that ll satisfy you.
kumaran v

3

That is an event that is used to do something in “intervals” not at a
specified time.

4

Bobby/Arne,

If you do that, you should be aware that it is going to seriously bog down
whatever box it is running on. The timer in Access, if I remember
correctly, is basically a little loop that runs in a circle doing nothing
but testing the millisecond counter until it reaches the specified count.
That would mean that your CPU Usage would go through the roof and nothing
would really be happening.

By far the best solution is the one involving the scheduler and the
specialized app that executes the query and then shuts down.

Respectfully,

Ralph D. Wilson II
Senior Programmer Analyst
Information Technology Department
9311 San Pedro Suite 600
San Antonio TX 78216
(800) 527-0066 x7368
(210) 321-7368 (direct)
ralphwilson@swbc.com

“hodge via vb-access-l”
08/03/2006 02:56 PM
Please respond to
vb-access-l@Groups.ITtoolbox.com

To
Ralph Wilson
cc

Subject
RE: [vb-access-l] How To Create A Timer In VBA To Run A Query

Try setting the timer to fire every minute (60000, I believe). When
the
timer fires, compare the time to a pre-determined time. If it matches,
then
do the processing, otherwise exit.

Bobby

5

You might make a project. My example is simple but shows the notion of
what you can do

When the main for opens it sets a time you could increase the interval as
the testing does not have to happen too often if it us just running a
query around 5:30 pm. This timer will fire and do nothing until the
critacial time is hit. in my case 5:42:30 PM" which seems a fine time to
run the qurey. It then sets a flag so it will not run the query again and
then calls a sub where you could put your code to run the query. it then
continues to loop, but does nothing as long as the date and the startdate
are the same, When the date rolls over it resets the flag to run the query
and continues checking until the appropreate time.

this steels a bit of time every interval thus unless you need tight times
you might make the interval fairly large. Or perhaps have two time checks
one that is for big interval and one that is smaller for when we get close
to the time.

You could start this app in a number of ways. launch it when windows starts.

HTH

Michael McCaffrey

Option Explicit
Dim fGoRun As Boolean
Dim StartOn As Date

Private Sub DoTime_Timer()
If fGoRun = True Then
If Time() > “5:42:30 PM” Then
fGoRun = False
RunQuery
End If
End If
If StartOn < Date Then
fGoRun = True
End If
Me.Text1 = Format(Now, “YYYY/MM/DD HH:NN:SS AMPM”)
DoEvents
End Sub

Private Sub Form_Load()
fGoRun = True
Me.DoTime.Enabled = True
Me.DoTime.Interval = 1000
StartOn = Date
End Sub

Sub RunQuery()
MsgBox “I have run somethin”
End Sub

Arnold,

The flaw in your plan is that you have to be sure that the Access
application is running at whatever time you want the query to execute .
. . and how do you do that without using the scheduler? :wink:

Respectfully,

Ralph D. Wilson II
Senior Programmer Analyst
Information Technology Department
9311 San Pedro Suite 600
San Antonio TX 78216
(800) 527-0066 x7368
(210) 321-7368 (direct)
ralphwilson@swbc.com

" arnold.mahlu… via vb-access-l"
08/03/2006 02:23 PM
Please respond to
vb-access-l@Groups.ITtoolbox.com

To
Ralph Wilson
cc

Subject
RE: [vb-access-l] How To Create A Timer In VBA To Run A Query

Thanks Lynn, I’ve already set that up but I’m looking for an exclusively
internal function or subroutine that will also do that without having to
rely on “task scheduler.” I believe it can be done using the “on timer”
property in a form field but don’t have specifics.

Arne

“lpfannenstiel via vb-access-l”
08/03/06 10:41 AM >>>

I have a task, db update, which needs to run every nite. I set up an
Access database having an autoexec macro that runs my desired task.
Within Windows XP, I have a scheduled task that runs at 2am every
morning, opening the Access db, running the autoexec macro, which
fires
the desired activity, qry, whatever.

Hope that helps.
Lynn

6

Only problem with using a timer is that it can only be set to a maximum of
32767 ms or slightly more than 32 minutes. Since there are 86400 seconds in
a day you would need several timers.
I would have a timer execute every minute and check the time using the now()
function
If it is the time you want run your query.
Bill

Bill Calkins
E-mail: b.calkins@taxrates.com
Main Office: (630) 879-6550
Fax: (630) 879-6551

12 W. Wilson
Batavia, IL 60510

7

Arnold,

The flaw in your plan is that you have to be sure that the Access
application is running at whatever time you want the query to execute .
. . and how do you do that without using the scheduler? :wink:

Respectfully,

Ralph D. Wilson II
Senior Programmer Analyst
Information Technology Department
9311 San Pedro Suite 600
San Antonio TX 78216
(800) 527-0066 x7368
(210) 321-7368 (direct)
ralphwilson@swbc.com

" arnold.mahlu… via vb-access-l"
08/03/2006 02:23 PM
Please respond to
vb-access-l@Groups.ITtoolbox.com

To
Ralph Wilson
cc

Subject
RE: [vb-access-l] How To Create A Timer In VBA To Run A Query

Thanks Lynn, I’ve already set that up but I’m looking for an exclusively
internal function or subroutine that will also do that without having to
rely on “task scheduler.” I believe it can be done using the “on timer”
property in a form field but don’t have specifics.

Arne

“lpfannenstiel via vb-access-l”
08/03/06 10:41 AM >>>

I have a task, db update, which needs to run every nite. I set up an
Access database having an autoexec macro that runs my desired task.
Within Windows XP, I have a scheduled task that runs at 2am every
morning, opening the Access db, running the autoexec macro, which
fires
the desired activity, qry, whatever.

Hope that helps.
Lynn

8

Try setting the timer to fire every minute (60000, I believe). When
the
timer fires, compare the time to a pre-determined time. If it matches,
then
do the processing, otherwise exit.

Bobby

9

Check the TimerInterval property of the form. Look in Access VBA help for
this.

Example
The following example shows how to create a flashing button on a form by
displaying and hiding an icon on the button. The form’s Load event procedure
sets the form’s TimerInterval property to 1000 so the icon display is
toggled once every second.

Sub Form_Load()
Me.TimerInterval = 1000
End Sub

Sub Form_Timer()
Static intShowPicture As Integer
If intShowPicture Then
’ Show icon.
Me!btnPicture.Picture = “C:IconsFlash.ico”
Else
’ Don’t show icon.
Me!btnPicture.Picture = “”
End If
intShowPicture = Not intShowPicture
End Sub

This is the sample code from the help topic.
George

George Elam
George Elam Software Services, LLC
gelam@gelam.com
832-654-5310

10

Thanks Lynn, I’ve already set that up but I’m looking for an exclusively
internal function or subroutine that will also do that without having to
rely on “task scheduler.” I believe it can be done using the “on timer”
property in a form field but don’t have specifics.

Arne

“lpfannenstiel via vb-access-l”
08/03/06 10:41 AM >>>

I have a task, db update, which needs to run every nite. I set up an
Access database having an autoexec macro that runs my desired task.
Within Windows XP, I have a scheduled task that runs at 2am every
morning, opening the Access db, running the autoexec macro, which
fires
the desired activity, qry, whatever.

Hope that helps.
Lynn

11

I have a task, db update, which needs to run every nite. I set up an
Access database having an autoexec macro that runs my desired task.
Within Windows XP, I have a scheduled task that runs at 2am every
morning, opening the Access db, running the autoexec macro, which fires
the desired activity, qry, whatever.

Hope that helps.
Lynn