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'.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.