# Solved: Schedule Access Query to run daily without intervention



## MargAtCanon (Jul 11, 2013)

I have a query in Microsoft Access 2007 which I want to run daily without my intervention. I have found the Task Scheduler in Windows but it only goes as far as opening Access.

I have also created a task in Outlook from which I can run the export query, but I can't find a way to make it happen without me - it just creates a reminder for me to do it.

I don't usually have Access open as I do all my work in Excel.
Any ideas?


----------



## draceplace (Jun 8, 2001)

Welcome to TSG Marge!
I have never done this myself but always wondered. I have an idea.
1. Set up a form that opens on default when Access opens and have the form run the query on load. You may need to make a seperate db that links to the table(s) and import the desired query. This would allow the normal use of the db because you don't want the query to run every time a user opens the db.

2. In the new form's 'on load event' create an event proceedure with something like this.
strQname1 = qryMarges_Query
DoCmd.SetWarnings False
DoCmd.OpenQuery strQName1, acViewNormal
DoCmd.SetWarnings True
DoCmd.Quit

3. Set your task scheduler to open this access db.

This will only run (I think) if you are logged in. To test this you should be able to just open the db. This may happen quickly, so you might comment out the DoCmd.quit until you can verify that it works like we want.

To open the db and not run the form/query hold the shift key down when you dbl click the the db icon.


----------



## MargAtCanon (Jul 11, 2013)

Thanks for your time. I have not yet tried your solution as we are having other issues, so it's not practical to set this up yet. However, I will keep your suggestion and put it into action when possible.
thanks again.


----------



## draceplace (Jun 8, 2001)

Let us know how this turns out. I've seen the question before but never seen anyone implement.


----------



## MargAtCanon (Jul 11, 2013)

I found a great solution to this problem.
I have set up a Rule in Outlook to run a script when I receive an email with particular subject.

Here's how.

In Outlook Alt F11 to open VBA

Insert a new Module
Create a macro in that Module as follows:


```
[FONT=&quot]Sub OpenMSAccessFile()[/FONT]
  
  [FONT=&quot]' Open MS Access File and run macro to export CPC Backorders to file on Network drive[/FONT]
  [FONT=&quot]' Written by Margaret Henderson 17/7/13[/FONT]
  [FONT=&quot]' To run automatically when the 2nd completion alert message is received in Outlook[/FONT]
  
  [FONT=&quot]    Dim AccessApp As Object[/FONT]
  [FONT=&quot]    Dim fileName As String[/FONT]
  
  
  [FONT=&quot]'    On Error Resume Next[/FONT]
  
  [FONT=&quot]'    Path = "[COLOR=red]ENTER YOUR OWN PATHNAME HERE[/COLOR]"[/FONT]
  [FONT=&quot]'    MSAccessFileName = "YOUR_DATABASE_NAME.accdb"[/FONT]
  [FONT=&quot]    fileName = "[COLOR=red]YOUROWNPATHNAME[/COLOR]\YOUR_DATABASE_NAME.accdb"[/FONT]
  
  [FONT=&quot]    Set AccessApp = CreateObject("Access.Application")[/FONT]
  [FONT=&quot]    With AccessApp[/FONT]
  [FONT=&quot]        .AutomationSecurity = msoAutomationSecurityLow[/FONT]
  [FONT=&quot]        .Visible = False[/FONT]
  [FONT=&quot]        .OpenCurrentDatabase fileName[/FONT]
  
  [FONT=&quot]'        .DoCmd.OpenQuery "Delete_Table"[/FONT]
  [FONT=&quot]        .DoCmd.RunMacro "ExportCPCBackorderReport"[/FONT]
  
  [FONT=&quot]        .AutomationSecurity = msoAutomationSecurityForceDisable[/FONT]
  [FONT=&quot]        .CloseCurrentDatabase[/FONT]
  [FONT=&quot]        .Quit[/FONT]
  [FONT=&quot]    End With[/FONT]
  
  [FONT=&quot]    Set AccessApp = Nothing[/FONT]
  
  [FONT=&quot]    MsgBox "Exporting CPC Backorder Report from MS Access is done.", , BISGmsg[/FONT]
  
  [FONT=&quot]    If Err <> 0 Then[/FONT]
  [FONT=&quot]        MsgBox "Can't find or open file(s): " & fileName, vbCritical, "Error"[/FONT]
  [FONT=&quot]    End If[/FONT]
  [FONT=&quot]End Sub[/FONT]
```
Put this code in the "ThisOutlookSession"


```
[FONT=&quot]Public Sub ShowMessage(Item As Outlook.MailItem)[/FONT]
  [FONT=&quot]'add code here[/FONT]
  [FONT=&quot]Call OpenMSAccessFile[/FONT]
  
  [FONT=&quot]End Sub[/FONT]
```
Then set up the Rule using the Rules Wizard.
Tick: on this machine only, with specific words in the subject and from people or distribution list
Next
Tick: run a script
Finish

The beauty of this is I have been able to replicate it on another user's computer so if I'm away it will run from her's as well. It doesn't matter if it runs twice.


----------



## draceplace (Jun 8, 2001)

Excellant! Thanks for posting this solution. Please mark the thread as solved.


----------



## Rollin_Again (Sep 4, 2003)

The easiest way to do this is to put your macro code in a stand alone module in your Access database and then use Windows Task scheduler to call a VBS script which will open the database and call the macro.

Rollin


----------



## MargAtCanon (Jul 11, 2013)

Thank you Rollin-Again, I had tried that solution but itwas not appropriate because I had to wait until other jobs had run before I could run my macro. Using Outlook has been the best solution.


----------

