Run one query, then another, then another...

[ajetrumpet]

this thread is getting lengthly mr. access. need some assistance?

 

[vbaInet]

this thread is getting lengthly mr. access. need some assistance?

We're having a bit of a banter here Adam, so you're more than welcome to rejoin in the discussions and make some interesting contributions

 

[ajetrumpet]

We're having a bit of a banter here Adam, so you're more than welcome to rejoin in the discussions and make some interesting contributions

i am a boring guy and I offer nothing interesting. so go ahead and fight to the death on it....good luck!

 

[vbaInet]

i'm sure most would disagree. the OP would be would delighted to hear more ideas.

 

[jomuir]

All sorted

Thanks!!

 

[vbaInet]

Just out of curiousity and for the benefit of readers that may stumble upon this thread, what approach did you eventually take?

 

[jomuir]

I went with this solution, as it shows an hour glass and also provides a description of what is happening....

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 True, "Running query " & i + 1 & " of 4 please wait...."
myDb.Execute myQueries(i)
Next

DoCmd.Hourglass False
End Sub

 

[vbaInet]

Looks good One thing though, remember your error handling code.

Thanks for posting back with your success.

 

[jomuir]

Very bad....but I never have put in error handling, but just read up on it and it make sense to have!! However, after thinking it would be easy to add this fails "Label not defined" and highlights the second line.....any ideas?

Private Sub RunAllQueries___Click()
On Error GoTo Err_RunAllQueries
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 True, "Running query " & i + 1 & " of 4 please wait...."
myDb.Execute myQueries(i)
Next

DoCmd.Hourglass False
Exit_RunAllQueries:
End Sub
Err_RunAllQueries:
MsgBox Err.Description
Resume Exit_RunAllQueries
End Sub

 

[vbaInet]

DoCmd.Hourglass False
Exit_RunAllQueries:
End Sub

Change that to Exit Sub. You end a routine/function, but exit if you want to "break" out of it.

 

[vbaInet]

Err_RunAllQueries:
    MsgBox Err.Description
[COLOR=Blue]DoCmd.Hourglass False[/COLOR]
    Resume Exit_RunAllQueries
End Sub

The blue was the main reason I reminded you of error handling. include that too.

 

[jomuir]

Cheers, that makes sense!!

 

[vbaInet]

No worries. Actually put that before the message box.

Have fun with the rest of your db.