Autorun Query

[Guest]

Hi

I am looking to run a query automatically at a given time during the day.
Can anyone help in this.

Thanks
Ian

 

[John Vinson]

Hi

I am looking to run a query automatically at a given time during the day.
Can anyone help in this.

Thanks
Ian

What I've done in this circumstance is to have a Macro in the database
which does a RunQuery (or RunCode) action, and use Windows Schedular
to open the database; use the

/x macroname

runtime switch on the command line in the scheduler to launch the
macro.

John W. Vinson[MVP]

 

[Guest]

Klatuu suggests the following:
Create a Macro that runs the export/query/code.
Use Windows Task Scheduler to schedule the event.
Use the /x macroname command line option to cause the macro to execute when
your mdb is opened.

The command line would look something like:
"D:\Program Files\Microsoft Office\OFFICE11\msaccess.exe" "C:\Documents and
Settings\whittlej\Playground.mdb" /X macRunQuery

This will open up Access and run the query. If you already have the database
open, it will open up another instance of it. Of course your computer needs
to be on. Also if you are getting the stupid security warning, that will
block it.
------------------
Here's another way that requires the database to be already open:

Create a form named frmTimer. In the On Open Event put the following code:

Private Sub Form_Open(Cancel As Integer)
Me.Visible = False
End Sub

In the Timer Interval event put 3600000 which is one hour.

Put the following code in the On Timer Event with the proper hour and
commands. It includes examples of exporting a spreadsheet and running a
query. Watch out for wrapping on the longer lines:

Private Sub Form_Timer()

'Runs at 10 pm. Change the 22 to the 24 hour value when you want it to run.
If Hour(Now) = 22 Then
DoCmd.SetWarnings False
' DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table
Name", "E:\SheetName.XLS"

DoCmd.OpenQuery "YourQueryName"

DoCmd.SetWarnings True
End If

End Sub

Make or modify a macro named AUTOEXEC that opens frmTimer. Both Access and
this form must be open (but not necessarily visible) for the above to work.