ADO Async call to database with Recordset Object not Working

[Jason0001]

I am trying to perform an async call to an Oracle database. The code skips right through the execution of the statement (without running the sql), then hangs when I check to see if the recordset is at the end of file. The code is in Excel, but really could be in Access or even VBScript, for that matter. I have it set up to continue responding to system UI interactions while the sql is running, but the sql doesn't run until I do the "if rs.EOF" statement. Some of these statements take 2 hours to process, so I don't want to completely shut down Excel while this is happening. I'd rather at least have the ability to display the status bar to tell the user where the program is. Here's my code snippet: (Thanks!)

Dim cnCIS As New ADODB.Connection
Dim cmCIS As New ADODB.Command
Dim rs As New ADODB.Recordset
cnCIS.Open "#ODBC connection string#
cmCIS.ActiveConnection = cnCIS
Dim sql As String
sql = "#some sql statement#"
cmCIS.CommandText = sql
Set rs = cmCIS.Execute(, , adAsyncExecute)
Do While (cmCIS.State And adStateExecuting) = adStateExecuting
DoEvents
Loop
If Not rs.EOF Then Range("charges").Value = rs("amount")
'code hangs on the above statement
rs.Close
Application.StatusBar = "Charges complete"
DoEvents

 

[PHV]

Have a look here: thread707-793083

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Jason0001]

That should work, but unfortunately, the code doesn't actually execute the sql statement until the "if rs.EOF" line. When I do the:
******************************************
Set rs = cmCIS.Execute(, , adAsyncExecute)
Do While (cmCIS.State And adStateExecuting) = adStateExecuting
******************************************
lines, it just skips right through, as if it's not actually executing the statement, simply storing it for later execution. Unfortunately, I don't know how to add the async parameter to the execution when I'm doing the "if rs.EOF" test.