Solved

Acces "Run Query" Macro

Posted on 2012-04-02
4
384 Views
Last Modified: 2012-04-03
I would like to create a macro or some sort of VBA which will allow me to run a series of queries within an access DB.

The goal being to start the process then be able to walk away from my machine while the queries run in sequence.

Is there a somewhat simple way of accomplishing this?

Thank you for your help.
0
Comment
Question by:jtr209
4 Comments
 
LVL 9

Accepted Solution

by:
armchair_scouse earned 500 total points
ID: 37798268
Yes, you can most certainly do that, either in a macro or using a VBA procedure and a macro to call the VBA procedure.  I am presuming all of your queries are 'action' queries (except for the last one perhaps) in that you are updating/adding/deleting records.

N.B.  This answer addresses your question and does not cover such things as error handlers in the VBA, in case one of your queries has a problem, or using query transactions, which allow multiple changes to be rolled back if any of the separate queries fail.  These suggestions might be worth investigating if your situation will cause you data loss/difficulties of one of the queries should fail.

To do it in a macro
Go to Create -> Macro, and then in the Add New Action dropdown select 'OpenQuery'.  In the box that appears, type in the name of your query.  Leave the View and Data Modes alone.  Then go to the next Add New Action dropdown below and repeat the process above until you have specified all of your queries.  Then save the macro.

To do it using VBA (and a macro)
Go to the VBA Editor (press Alt+F11), and if you don't already have code modules in your database, go to the Project Explorer (the treeview thing in the top left hand corner), right click on the top 'node', i.e. the thing at the top of the tree, and from the context menu that appears, select Insert->Module.  In the module window, create your procedure, e.g.

Sub RunMyQueries

  DoCmd.OpenQuery "Query1"
  DoCmd.OpenQuery "Query2"
  DoCmd.OpenQuery "Query3"

End Sub

Save the module so that the procedure is saved.  Then go to your main Access window, select Create->Macro, and in the macro, in the Add New Action dropdown, select RunCode.  In the box that appears, type the name of the procedure you have created, then save the macro.  Now running this macro will run the VBA procedure you have created.
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 37799937
You may want to add before and after:

docmd.setwarnings false
.
.
.
docmd.setwarnings true

That way the process won't stop waiting for your response.

Scott C
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37801019
<No Points wanted>
But Please note what armchair_scouse states:
    "in case one of your queries has a problem, or using query transactions, which allow multiple changes to be rolled back if any of the separate queries fail. "

...This is almost inevitable when a "Series" of queries is run.
0
 

Author Closing Comment

by:jtr209
ID: 37803473
Thank You
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now