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

Can I setup queries to run automatically?

Dear all,

I have a set of 30 queries that have a running time of 3 min each.  Is it possible to set the queries to run automatically?  

I'm also selecting ok to the following messages:
- 'There isn't enough memory to undo the operation etc'
- 'Do you want to update x amount of records'.

3 Solutions
You do not have an SQL Agent equivalent in Access. However, you can automate the process with a help of a simple VBS script that can be run on a scheduler. The script would open your database and execute the queries. The script can be scheduled via the OS's Task Scheduler
Within Access, you can create a VBA procedure inside a module to run the queries.  You then can manually run this process or call it from a macro or a command button in a form.  The code would look something like this:
Public Sub MyProcessName()
    Dim db As Database
    Set db = CurrentDb

    db.Execute("NameOfQuery1"), dbFailOnError
    db.Execute("NameOfQuery2"), dbFailOnError
    db.Execute("NameOfQuery3"), dbFailOnError
    Set db = Nothing
End Sub

Open in new window

Since this uses the Execute method, you won't get the warning dialogs that popup.

As an aside, you probably should post a question on speeding up your queries. We have a nightly process that runs over a hundred queries and procedures altogether with a total running time of approx 40 minutes. And these queries are to linked Oracle tables with records in the hundreds of thousands to millions.  I have a gut feeling that your queries can be optimized.
In addition to IrogSinta's comment...

You can place code like that in the open event of a form and set that form as your 'startup' form so that it opens automatically when your database is opened.  Alternatively you can create a macro named 'AutoExec', and CALL the sub or function from that macro.  This macro will automatically run when your database opens.

With either of those options, you can include a quit command.  If you then use windows scheduling to run these databases nighly, the databases will open, run the code and then closes themselves when done.
As an aside,

<< 'Do you want to update x amount of records'.>>

The Execute method IrogSinta is suggesting will suppress these add and update messages.

<< 'There isn't enough memory to undo the operation etc'  >>

However, there are a variety of these memory-related messages that you may not be able to suppress.
AndyC1000Author Commented:
Thanks for your responses.

IrogSinta's sample code worked with sample queries hopefully the out of memory prompts will be supressed.

I will post another question about scheduling and optimisation of my queries.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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