Create a module or function to run queries in specified order 1

[jrlss]

I’m a newbie and needed assistance with creating a function (or module) to run Access queries in a specified order. The queries are built to automatically create their own tables, so there is no need to specify output location or work with the queries in any other way. I’d like to be able to do two things:

1) Have the function (or module) run each query from my database (adhocjobs.mdb)*.
Examples of query name & order:
Telephone_number
Email_address
Spouse_info

2) Add this function/module in other databases by copying & pasting it’s code into other dbases--to run independently on each database. (could you give instructions on how to do this?)

I've reviewed a few threads that are related to this, but am not sure how to customize/extact code for my situation. Thank you for any assistance you can lend. So far the threads I've found on this site is a great resource and I greatly appreciate your input.

*NOTES/ADDITIONAL INFO:
I’ve never written a function (or module) before, but don't mind trying if you can give me pointers
Currently running Microsoft Office Professional Plus 2010
If you need it, there is a unique identifier in each table: Dnr_ID;
Database & query environment is an ODBC type where we link to tables & run queries

 

[sxschech]

How do you plan to run the code, by using a button on a form or do you plan to run the code directly? As for the code to run the queries in sequence:

DoCmd.SetWarnings False
docmd.openquery "Telephone_number"
docmd.openquery "Email_address"
docmd.openquery "Spouse_info"
DoCmd.SetWarnings True

The SetWarnings turns off the prompts such as "Your are about to run a ..."

Once you decide on whether this code will go behind a button or you will run it directly, can give you some more information.

 

[jrlss]

Thank you for your prompt response!!

I plan on running the code directly from my database. The reason is based on the results there are a few manual steps (queries to run), then the final output is sent to the user as an excel file.

Could I also ask how to use the code & what it is (ie: is it considered a function or would it be a module)? Also, will this give me some type of a message when the function/module has completed?

Thank you again for your willingness, it will save me hours of time!

 

[sxschech]

If you want to run this without a form, then yes you would create a module for it. It would be easier to run it from a form though. Create a blank form
Add a button
The label could be "Run Queries"
The On Click event would be an event procedure
Click on the triple dots to bring up the vba window

Paste in the code between Sub and End Sub:

DoCmd.SetWarnings False
docmd.openquery "Telephone_number"
docmd.openquery "Email_address"
docmd.openquery "Spouse_info"
DoCmd.SetWarnings True
MsgBox "The queries have finished" , vbOKOnly, "Task completed"

Let me know how it goes.

 

[jrlss]

Ok, I've called myself cutting & pasting it between
'Private Sub Form_Click()' and
End Sub

Nothing seemed to happen..went & tried pasting between End Sub in the last line above, but I have a feeling I've messed up something: it appears to be placing itself in a module my boss already created because in the title line of the vba window the title is option compare database (and it has changed itself to General & Declaratin)
How do I:
1) undo what I've pasted, and more importantly
2) get a new vba window that will not connect to a pre-existing module?
The cut & paste of what I'm seeing (I added different queries-GVGbyoll...):
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Option Compare Database

Private Sub Form_Click()


End DoCmd.SetWarnings False
DoCmd.OpenQuery "GVGbycoll1"
DoCmd.OpenQuery "GVGbycoll2"
DoCmd.OpenQuery "GVGbycoll3"
DoCmd.SetWarnings True
MsgBox "The queries have finished", vbOKOnly, "Task completed"Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

How would this have 'appended' itself to her module? Have I ruined the module she's created?
Unfortunately the system is scheduled to be down tonight thru the weekend, so I'll be able to respond to your posts but little else.
I'm still very thankful for what you're attempting to steer me thru and look forward to hearing further suggestions.

 

[sxschech]

Been in meetings today, so will respond tomorrow. Hope that's ok.

 

[sxschech]

Your code looks like you added a click event to the form itself rather than to a button. Try adding a button onto the form. If you use the default button creation from Access, (These steps are for 2007 version: First have the form open in design view, click on Design Tab and then click on "Button" from the Ribbon Menu, then click where you want it to show up on the form, when the Command button wizard shows up, click on cancel.)

Your code should look more like this:

Private Sub [s]Form_Click()[/s] Command0_Click()

[s]End[/s] 
DoCmd.SetWarnings False
DoCmd.OpenQuery "GVGbycoll1"
DoCmd.OpenQuery "GVGbycoll2"
DoCmd.OpenQuery "GVGbycoll3"
DoCmd.SetWarnings True
MsgBox "The queries have finished", vbOKOnly, "Task completed"

[red]End [/red]Sub

You can change the name of the button to something more meaningfull than command 0 in the properties window of the button on the form. If you change the name of the button, you will also need to change the name of the sub to match, for example if you call your button RunQueries then you change your sub from

Command0_Click to RunQueries_Click


If you are adding code to an existing form and your boss already had code in the form, then yes, your could would be added onto what was already there.

 

[jrlss]

Wow! This is simply amazing....I did find out that I was applying everything to the form (not the button), made an adjustment & just ran it with only one query (commented out the others). It ran wonderfully. YOU TOTALLY ROCK!!!!!

If its not too much of a bother, would you be willing to answer a couple more questions:
1) Do I need to adjust the timeout settings? I timed & compared the results and they seem fine, but I'm worried about joining all of the rest of the queries and if it might need some tweeking for run times(there are 18 queries).
2) The form doesn't seem to give you the nice run time approximation (scale at the lower right hand corner), so I don't know how far into the job or the job's progress is being made. Is there a remedy for this?
3) Finally......a) how do I get the TGML to work, all I see is a page of codes bold and I can't seem to make it work.
4) Be sure to know that I'd like to get a vote out for you....is it hard to do?
AGAIN MY THANX, you've guided me thru everything and even seemed to know what I was thinking before it was asked. KUDOS to you!!!!

 

[Andrzejek]

To display in a label your progress, you may place a label on your Form, and if its name is Label1:

DoCmd.SetWarnings False[blue]
Label1.Caption = "Running GVGbycoll1..."
Label1.Refresh[/blue]
DoCmd.OpenQuery "GVGbycoll1"[blue]
Label1.Caption = "Running GVGbycoll2..."
Label1.Refresh[/blue]
DoCmd.OpenQuery "GVGbycoll2"[blue]
Label1.Caption = "Running GVGbycoll3..."
Label1.Refresh[/blue]
DoCmd.OpenQuery "GVGbycoll3"
DoCmd.SetWarnings True[blue]
Label1.Caption = "I am done running."[/blue]
MsgBox "The queries have finished", vbOKOnly, "Task completed"

Have fun.

---- Andy

 

[sxschech]

If you use Andy's approach, then you may not need the msgbox as you could have the final message to indicate that it is done, however, if you use the form for other purposes besides running the queries, then you may want to keep the message box and then add one more set afterwards so that it clears out the message.

Label1.Caption = "I am done running."
MsgBox "The queries have finished", vbOKOnly, "Task completed"
Label1.Caption = ""
Label1.Refresh

To use the markup you need a slash with the ending code, something like

{b}bold{/b} with square brackets of course, would do bold. You can click on Process TGML link under Step 2 Options of "Your Reply" to get a window showing the different markup commands.

There is code out there for progress bars, but I would probably go with Andy's approach as it's easier, only thing is it won't tell you how long only that it has finished that particular query.
You could, if you've run through the series, take note of the time it takes to run each query and add that to the caption code, such as:

Label1.Caption = "Running GVGbycoll1, please allow 3 minutes to complete..."

 

[jrlss]

Andy,

Once I've placed my button and put the event code in it, it doesn't seem to want to accept a label (label has no associated control to it). Is there another way to approach this?
My thanks for your response, your label caption would really be nice to have.

 

[sxschech]

You would need to add a label to your form, similar to adding a button, except you would choose Label from the design ribbon. Label icon is "Aa

 

[jrlss]

Great! Leaving for the holiday weekend & will try all next week. THIS IS A SUPER GROUP & I hope you all have a good weekend!
Thanks again for all the help!

 

[sxschech]

Glad to help. Let us know how it goes and have a good weekend too.