Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 413
  • Last Modified:

Acces "Run Query" Macro

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
jtr209
Asked:
jtr209
1 Solution
 
armchair_scouseCommented:
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
 
clarkscottCommented:
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
 
Jeffrey CoachmanCommented:
<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
 
jtr209Author Commented:
Thank You
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now