Automate running several make table queries

S

SimonJester

I tried to find this in the archives, but I probably didn't word the
search right. Maybe someone here could help. I would really
appreciate it.
I have 5 queries that I would like to automate the running of. Each
query has some kind of criteria that it asks for. I would like to come
up with some process (a button or something) that I would only have to
click on once that would run all of these queries one after another.
Is this possible?
Thanks for your time.
 
S

Sergey Poberezovskiy

When you have DAO library linked:

Place the following code into your button's Click event:

On Error GoTo proc_Err
Dim qdfs As QueryDefs

Set qdfs = CurrentDb.QueryDefs
With qdfs(queryName1)
.Parameters(0) = param1
...
.Parameters(n) = paramN
.Execute dbFailOnError
End With
....
With qdfs(queryName5)
.Parameters(0) = param1
...
.Parameters(k) = paramK
.Execute dbFailOnError
End With
Exit Sub
proc_Err:
MsgBox Err.Description
End Sub

You can event wrap it around in a Transaction so that
either all your queries will execute or no changes will be
made (if any of them fails).
If you use ADO then the code will be similar, with use of
Command objects rather than QueryDef.

HTH
 
W

Wayne Morgan

Yes, it is possible. You will need some VBA code in the Click event of the
button. Depending on the criteria you're wanting to supply, there is more
than one way to do that.

The code to run the queries would be similar to:

Dim db As DAO.Database
Set db = CurrentDb
db.Execute "NameOfQuery", dbFailOnError
'Repeat above line for each query
Set db = Nothing

To input the parameters, you can just let the queries prompt for them or you
can supply them using a form that you pop-up prior to running the queries.
Have text boxes on the form that you fill in and a button to click to
continue when you're ready. When you click the button, hide the form
(Visible = False) to let your code continue running (this will leave it open
so the queries can see it). For the parameters in the queries, point them at
the text boxes on the form. Close the form after running the queries. To
pause your code when you open the form use the acDialog window mode
argument. This will pause the code that called the form until you close or
hide the form.
 
L

Larry Daugherty

Sure. On the form with the command button to run the report create 5
textboxes. Enter the required parameter for query n into textbox n. In the
criteria line of each query that presently requests a parameter, replace the
existing request for parameter entry with a reference back to the
appropriate textbox on the launcher form. Note that the form must remain
open.

[forms!frmMyForm!MyControl]

Then put the 5 lines to run the 5 queries in the OnClick event procedure.

HTH
 
Top