Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Acces "Run Query" Macro

Posted on 2012-04-02
4
Medium Priority
?
407 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
[X]
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 Comments
 
LVL 9

Accepted Solution

by:
armchair_scouse earned 2000 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

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.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

670 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