Hi all
I would like to get automatically run an append query at a certain time every day. I am fairly conversant with VBA in Excel but I am struggling with Access VBA, any help or suggestions of where to look gratfully received,
thanks in advance
Another way if your DB is running all the time. You can use the Windows API Timer Functions. This is not a Forms' Timer Event but works in a very similar fashion.
Place the following code into a Database Code Module:
Code:
Declare Function SetTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Declare Function KillTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long
Public Sub MyTimerProcedure(ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long)
[COLOR="DarkGreen"]'A Static Integer Variable used as a flag to
'indicate whether or not our query was fired.[/COLOR]
Static Fired As Integer
[COLOR="DarkGreen"]'If the Computer time is greater than 12:00:00AM
'and less than 12:01:00AM then fire the Append
'Query. Because the API Timer is set to fire this
'procedure every 1 second, the Fired variable is
'set to 1 so that the Query is only run once. We
'are providing 10 seconds to ensure the Query is
'run.[/COLOR]
If (Time > #12:00:00 AM# And Time < #12:00:10 AM#) And Fired = 0 Then
Fired = 1
CurrentDb.Execute "YourAppendQueryName", dbFailOnError
End If
[COLOR="DarkGreen"]'Once the ten seconds after midnight are up, we
'reset the Fired flag to 0 so that the next time
'this procedure is fired and the time falls between
'12:00:00AM and 12:00:10AM the Query will be run
'again.[/COLOR]
If Time >= #12:00:10 AM# And Time < #12:00:20 AM# Then Fired = 0
End Sub
To start the timer you need to place this line of code either into the OnClick event of a Command Button or into the database Start Forms' OnOpen event like this:
Code:
Private Sub Form_Open(Cancel As Integer)
[COLOR="DarkGreen"]'Set the API Timer to fire the TimerProc Sub-Procedure
'every 1 second (1000 miliseconds = 1 Second).[/COLOR]
SetTimer Me.hwnd, 0, 1000, AddressOf MyTimerProcedure
End Sub
And to Destroy the Timer, place this code line into the Forms' OnUnload event:
Code:
Private Sub Form_Unload(Cancel As Integer)
[COLOR="DarkGreen"]'Destroy the API Timer.[/COLOR]
KillTimer Me.hwnd, 0
End Sub
Thanks for the help CyberLynx,
can you confirm I understand this correctly, you only need to start the timer function if the database has been shutdown, otherwise the timer will run will run automatically until stopped?
Hi Wayne
after a lot of messing around with the file path I got the task scheduler to run. When I run the task with just the first part of the path ("C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE") a new database opens, when I add the filepath to the database I want to open and run the macro from the task scheduler says it is running but nothing appears to happen. I have run it without the reference to the macro and get the same thing. Sorry to be a pain but any ideas??
"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "\\F:\Copy of Replica of Div 21.mdb"/x"Test"
can you confirm I understand this correctly, you only need to start the timer function if the database has been shutdown, otherwise the timer will run will run automatically until stopped?
Another way if your DB is running all the time. You can use the Windows API Timer Functions. This is not a Forms' Timer Event but works in a very similar fashion.
Place the following code into a Database Code Module:
Code:
Declare Function SetTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Declare Function KillTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long
Public Sub MyTimerProcedure(ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long)
[COLOR="DarkGreen"]'A Static Integer Variable used as a flag to
'indicate whether or not our query was fired.[/COLOR]
Static Fired As Integer
[COLOR="DarkGreen"]'If the Computer time is greater than 12:00:00AM
'and less than 12:01:00AM then fire the Append
'Query. Because the API Timer is set to fire this
'procedure every 1 second, the Fired variable is
'set to 1 so that the Query is only run once. We
'are providing 10 seconds to ensure the Query is
'run.[/COLOR]
If (Time > #12:00:00 AM# And Time < #12:00:10 AM#) And Fired = 0 Then
Fired = 1
CurrentDb.Execute "YourAppendQueryName", dbFailOnError
End If
[COLOR="DarkGreen"]'Once the ten seconds after midnight are up, we
'reset the Fired flag to 0 so that the next time
'this procedure is fired and the time falls between
'12:00:00AM and 12:00:10AM the Query will be run
'again.[/COLOR]
If Time >= #12:00:10 AM# And Time < #12:00:20 AM# Then Fired = 0
End Sub
To start the timer you need to place this line of code either into the OnClick event of a Command Button or into the database Start Forms' OnOpen event like this:
Code:
Private Sub Form_Open(Cancel As Integer)
[COLOR="DarkGreen"]'Set the API Timer to fire the TimerProc Sub-Procedure
'every 1 second (1000 miliseconds = 1 Second).[/COLOR]
SetTimer Me.hwnd, 0, 1000, AddressOf MyTimerProcedure
End Sub
And to Destroy the Timer, place this code line into the Forms' OnUnload event:
Code:
Private Sub Form_Unload(Cancel As Integer)
[COLOR="DarkGreen"]'Destroy the API Timer.[/COLOR]
KillTimer Me.hwnd, 0
End Sub
have just come across this, as im looking for a timer function to run a query at midnight and this looks like it would work very well just couple of things
you say copy into a code module you mean basic modules yes ?
CyberLynx's last activity was in 2010 according to his profile.
the first part of his code with the public sub would go into a standalone module and the two sections of code with the private subs would go into a form module.
