By Joseph Dannaoui - Ernst & Young Consulting
Stored Procedures. We all know the benefits and down-sides of using them. We know that you can do some weird and wonderful things them. This is my latest find.
As a part of rdoConnection and rdoResultset exists a property called StillExecuting. It returns a boolean value that checks whether a query or stored procedure is still executing. Until StillExecuting is set to false, you can not access the required connection or result set.
On the surface it doesn't seem like much. But where it really comes into play is when you have to do a lot of processing to be done in a stored procedure or query, and you want to return the focus back to the application immediately.
Here's an example:
sSQL = "exec xxx_temp" Set Res = conUser.OpenResultset(sSQL, rdOpenKeyset, rdConcurLock, rdAsyncEnable) While Res.StillExecuting 'It's probably a good spot to increment a Progress bar 'just to let the application user 'know that the query is still in action. DoEvents Wend Res.Close
The key here is rdAsynEnable. This allows you to send asynchronous SQL statements to your database. By doing this, you can give control back to the application, allowing for other changes in the application (like updating the progress bar). If that was not set here, the While statement would not of executed until a result set was returned, making the While statement pointless.
Now that example is really cool if you are returning some data, but what about when you are running multiple queries or calculations that cannot return a single result set? Easy. Just run an execute example against the connection.
sSQL = "exec MYSUPERDUPERSTOREDPROCEDURE" MyConnection.Execute sSQL, rdAsyncEnable While MyConnection.StillExecuting DoEvents Wend
As a tip, if the user wishes to end the execution, use the Cancel method to stop processing. You can also use the StillExecuting property for Data Access Objects.