Run one query, then another, then another... (1 Viewer)

[jomuir]

I would like to be able to run one query and when that has finished running another query automatically, and when that has finished running another query automatically starts........

I have about 10 queries that I run in order at the moment:-

01 – Append a table from another database2 to this one
02 – Add missing data to this new table
03 – Add missing data to this new table from a different database
04 – Append a table from another database3 to this one
05 – Create a new table with the required fields from table2 in this database
06 – Append table2 to the new table
.
.
.
10 – Find duplicate in table (should be none, just a check)

Ideally I would like to kick off one process/macro/query not sure what, but it would automatically run all the queries in the right order....is that possible?

 

[ajetrumpet]

create a macro and use the RUNQUERY command ten times in a row.

don't forget to use SETWARNINGS at the begin and end of the query run to supress the warning messages

 

[vbaInet]

create a macro and use the RUNQUERY command ten times in a row.

ajetrumpet, I didn't know you use macros?

If you prefer VBA, as an alternative, create a function to this. You create an array of type String to hold the names of your query and loop through that. I will start you off:

Private Function RunAllQueries()
    Dim myQueries() as String, i as Integer, myDb as Database

    Set myDb = CurrentDb    

    Redim myQueries(9)
    myQueries(0) = "1st Query"
    myQueries(1) = "2nd Query"
    ....
    ....

    For i = 0 to Ubound(myQueries)
        myDb.Execute myQueries(i)
    Next

End Function

Something along those lines. UNTESTED

 

[ajetrumpet]

ajetrumpet, I didn't know you use macros?

i never do, although i am occassionally known for using the auto exec beast.

by the way mr. wizard, CURRENTDB. prefix on vba commands is not trustworthy. to be absolutely sure it works, DIM a variable first and set it to currentdb.

see gemma the husky for that little piece of knowledge contribution!

 

[vbaInet]

i never do, although i am occassionally known for using the auto exec beast.

Haha! It is a beast isn't it. Be careful with the beast though

by the way mr. wizard, CURRENTDB. prefix on vba commands is not trustworthy. to be absolutely sure it works, DIM a variable first and set it to currentdb.

see gemma the husky for that little piece of knowledge contribution!

I've never had a problem with CurrentDb. I will amend the post according to your advice.

 

[ajetrumpet]

I've never had a problem with CurrentDb. I will amend the post according to your advice.

i didn't mean for you to do that inet. but it is a lesson i have learned. believe it or not, it HAS errored out on me before using currentdb. but only once.

 

[vbaInet]

i didn't mean for you to do that inet. but it is a lesson i have learned. believe it or not, it HAS errored out on me before using currentdb. but only once.

That's not a problem It's better the OP has the more reliable version of the code.

By the way, do you remember what sort of problems you encountered?

 

[ajetrumpet]

By the way, do you remember what sort of problems you encountered?

just one. it was either object required, or with block variable not set. can't remember which. made no sense at all

 

[vbaInet]

just one. it was either object required, or with block variable not set. can't remember which. made no sense at all

Alright, gotcha. I would assume it's the object required. Thanks

 

[jomuir]

ajetrumpet, I didn't know you use macros?

If you prefer VBA, as an alternative, create a function to this. You create an array of type String to hold the names of your query and loop through that. I will start you off:

Private Function RunAllQueries()
    Dim myQueries() as String, i as Integer, myDb as Database
 
    Set myDb = CurrentDb    
 
    Redim myQueries(9)
    myQueries(0) = "1st Query"
    myQueries(1) = "2nd Query"
    ....
    ....
 
    For i = 0 to Ubound(myQueries)
        myDb.Execute myQueries(i)
    Next
 
End Function

Something along those lines. UNTESTED

Thanks a lot this worked a treat!! I have never used Macro's so thought it was better to set up a form with comand buttons linked to these functions.

Thanks again!!

 

[vbaInet]

Goodie. Glad it worked for you.

 

[jomuir]

There is just one thing.... with so many queries running in the background, so of the users are complaining that they do not know when that function has finished or if it is still running (unless they click on the form and everything freezes or an egg timer pops up). Is there any way of getting an egg timer or a progress box to appear so that users know that the queries are still running and how far through the function they are?

If not is does not matter, but it would be nice to have

 

[vbaInet]

A couple of suggestions in order of preference.

1. Create a form that has an image of a padlock and timer and have a message like "Update in progress... " just below the image. You will open the form just before the line where you run the query, then close it just after all the queries have been processed. A msgbox will also pop-up just informing the user that the queries ran successfully. Set both the Modal and Pop-Up properties of the form to Yes, and also set the Border Style property to Borderless. Create a rectangle that fits around the edges of the form and make its Border Width 2 or 3. Remove record selectors, navigation buttons, dividing lines, scroll bars, control box, min/max buttons and close button. Set the Moveable property to No (just in case) and set the Auto Center to Yes. Remember to have error handling and in your error handling you MUST close this form.

2. Look into using the Progress Meter which appears on the Status bar.

3. If you don't want things to be too complicated then activate the Hour Glass before runnning the query and turn it off after. Also put this in the error handling to turn it off.

Docmd.HourGlass True

 

[jomuir]

That is great!!, thanks again.

Will look into the first one, the second option - status bar already showing, but nothing appears on it when running the function (the progress bar does appear when I run the queries one at a time)

The Third option, will start using straight away, while I am looking into the first.

Cheers!

 

[DCrake]

You can also use

DoCmd.Echo "Running query " & i & " please wait...."

prior to the MyDb.Execute

and

DoEvents After the above line

 

[vbaInet]

The progress meter needs to be initialised with the total number of records and updated by 1 as you go through the loop. If you want to provide real-time processing status it would be quite hard to do this using the progress meter since you don't know the position of the cursor on each record of each query. However, you could initialise the meter with the number of queries you're running (i.e. 10) and update the meter progressively after every query. By using that method the user can see how many more queries have been processed and how many are left. This link explains how it works:
http://support.microsoft.com/kb/210474

Just thought I should iterate, remember the Error Handler and set the Hour Glass to false in there just in case something fails whilst processing unless the Hour Glass would remain visible if one of your queries fails.

Have fun with this

You're welcome.

NB: David, I always forget about Echo. That's a good, simple and clean one.

 

[jomuir]

You can also use

DoCmd.Echo "Running query " & i & " please wait...."

prior to the MyDb.Execute

and

DoEvents After the above line

I tried this too, but got an error:- "An expression you entered is the wrong data type for one of the arguments. When I click debug, it highlights this line:- DoCmd.Echo "Running query " & i & " please wait...."

I have tired on a few functions, here is an example of one of my functions (well, vbaInet's functions)......

Private Sub RunAllQueries___Click()

' DoCmd.Hourglass True

Dim myQueries() As String, i As Integer, myDb As Database
Set myDb = CurrentDb
ReDim myQueries(3)
myQueries(0) = "0 - Clear Out yealry accamend - required tbl"
myQueries(1) = "0 - Clear Out yearly acc amend tbl"
myQueries(2) = "0 Clear out ALL DATA table"
myQueries(3) = "0 Clear out Merged_Stock Table"

For i = 0 To UBound(myQueries)
DoCmd.Echo "Running query " & i & " please wait...."
myDb.Execute myQueries(i)
DoEvents
Next

End Sub

 

[vbaInet]

Just a slight error in syntax. to turn it on:

DoCmd.Echo True, "Running query " & i & " please wait...."

Remember to turn it off and set the text to empty string at the end of NEXT:

DoCmd.Echo False, ""

 

[jomuir]

So some reason it hangs after I have run the function - I see the text flash up on the status bar, and then goes back to "Form View" but the whole of Access is frozen!!

I can work on everything else on my pc, but cannot do a thing with Access.

 

[vbaInet]

IGNORE that last bit about turning it off, my bad.

Just keep it turned on.

So the only line you would need to amend in your post #17 is :

DoCmd.Echo True, "Running query " & i & " please wait...."