Solved

Acces "Run Query" Macro

Posted on 2012-04-02
4
395 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 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

739 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