Solved

Acces "Run Query" Macro

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Access 2016 Debugging 7 42
What logic to build in order to get a weekly reminder 9 36
Format a Field AFTER UPDATE 5 18
access 7 0
MS Access 2003 or later To MySQL Migration Project Hello All, this is my second article in the category of MS-OFFICE Automation. In internet I am not able to find any comprehensive resource on the Migration of MS Access back-end to MySQL so I fin…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

932 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

21 Experts available now in Live!

Get 1:1 Help Now