How to control/pause SQL Query Execution?

Hello,

I have a number of QueryTables I am using to extract data from a remote server and database. The queries are actually dependent on the outcome of several stored procedures I am running, which I execute within my macro and for whose executions I use a sql string for a QueryTable (which is perhaps the starting point in my problem).

The trouble is, I need to execute these stored procedures in order, making sure each has successfully completed its task, before going on to the next one. To illustrate, this is a sample execution outline:

Using MyQueryTable, I do this:

Step 1 - With MyQueryTable
.Sql = "exec sproc1"
.Refresh
End With

Step 2 - With MyQueryTable
.Sql = "exec sproc2"
.Refresh
End With

Step 3 - With MyQueryTable
.Sql = "exec sproc3"
.Refresh
End With

Step 4 - With My QueryTable
.Sql = "select * from mytable"
.Refresh
End With

For stored procedures 1-3 above, #2 depends on the tasks completed by #1 and #3 must follow on the tasks completed by #2. However, the way the code is currently set up, it is clear that I have no control over the timing of these commands so that they only execute upon the completion of the prior command.

I have been looking into other ways of calling these stored procedures, but I'm not sure how to go about doing that (there would appear to be outdated ways of doing it via DAO, but then my Excel implementation does not appear to support certain things I would expect -- like 'Application.OfficeDataSourceObject' -- to instantiate a data source and get on with the rest).

I realize my code above is a hack since I'm really using Query Tables to execute stored procedures, but does anyone know how I might control execution flow while doing the above (as in pausing or requesting a 'success/failure' argument from each step before proceeding to the next) or a better way I could execute these stored procedures in their required succession?

Any help/advice would be much appreciated.

-Pete

Hi Pete

I've never used QueryTables to execute stored procedures before so I have no certainty this will work but call the Refresh with BackGroundQuery:=False ie:

Please Login or Register  to view this content.

This should set the code to synchronous execution (ie hopefully the query will execute before control is passed back to VBA to continue). This certainly works when data is being returned to the worksheet, I'm just not sure how it will work given the it's a stored proc being targeted - hopefully control won't be passed back as soon as VBA has done its bit but will wait until the procedure has run.

Richard

Hi Richard,

Thank you very much for your response.

I've tried your suggestion, but it looks like I'm still running into the same problems - it's a shame, because I'm pretty sure you're getting right to the heart of the matter (mostly the bugs come with the error message - query can't be run because its refreshing in the background).

I'm sure there must be a way to do this - any other ideas welcome.

Thanks again.

-Pete