• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2202
  • Last Modified:

Schedule a query in Access

Am just wondering how could we schedule a query / run macro in access database ?

  • 4
3 Solutions
Bob ScriverCommented:
You can use the OnTimer Event Procedure of a form that is always open to schedule this query run.  

In the OnTimer event control create some VBA code to monitor the system time (and Date if necessary) that will trigger the query to run when you want it to.  I use a small invisible dummy form that I open within the AutoExec macro and just sits up in the corner of the Users screen without them knowing about it.  It's only function is to perform this scheduling function I have described.  You have to set the TimerInterval to some reasonable interview for monitoring the time( say 15 min. or 900,000 milliseconds which what you have to use in this control)

Example of OnTimer Event Procedure:
Select Case Time()
   Case 8:00 AM
       DoCmd.SetWarnings False
       DoCmd.OpenQuery "qryUpdateTables"
       DoCmd.SetWarnings Ture
   Case 12:00 PM
       DoCmd.SetWarnings False
       DoCmd.OpenQuery "qryAppendNewData"
       DoCmd.SetWarnings True
End Select

I think you see what I mean here. This can just be a standalone application database that's sole function is to perform scheduled tasks for you or it may be something that triggers for each user and updates data to their particular database file.  

I hope this gives you some ideas to help solve your problem.
Bob ScriverCommented:
Update to my previous posting.  The Case statements should have read like this:
Select Case Time()
   Case Between 8:00 am and 8:15 pm

   Case Between 12:00 pm and 12:15 pm

end select

You would also need to update a table that indicates that the daily query process for that time has been completed and update it after it is complete so that it only happens once per day.  I will leave that up to you.
Bob ScriverCommented:
typos all over the place:
   Case Between 8:00 am and 8:15 am

Sorry, it is late at night here in Michigan.  I am going to bed.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accept question, points to scriverb
Please leave any comments here within the next seven days.
EE Cleanup Volunteer
Bob ScriverCommented:
The answer that I provided is an acceptable answer to this question.  I haven't been monitoring old postings but this one should be an accepted answer.

Bob Scriver
The correct syntax in VBA for Access 2003 is

     Case #8:00:00 AM# To #8:15:00 AM#

I'm not sure if the above code worked on older versions of Access or not.       I found this thread in my searching for the syntax so I wanted to post.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now