Schedule a query in Access

Posted on 2003-02-27
Medium Priority
Last Modified: 2010-10-07
Am just wondering how could we schedule a query / run macro in access database ?

Question by:sherp
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4

Assisted Solution

by:Bob Scriver
Bob Scriver earned 300 total points
ID: 8040206
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.

Assisted Solution

by:Bob Scriver
Bob Scriver earned 300 total points
ID: 8040215
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.

Accepted Solution

Bob Scriver earned 300 total points
ID: 8040222
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.
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

LVL 18

Expert Comment

ID: 8773929
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

Expert Comment

by:Bob Scriver
ID: 8774047
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

Expert Comment

ID: 33852956
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question