VBA ADO Connection to Oracle with Async

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

I've tried this a few different ways, but basically I just want to
connect to an Oracle database, retrieve a sql recordset asynchronously,
and doevents while I'm waiting. Unfortunately, executing the ADODB
command asynchronously takes only a second. When I try to test the
recordset to see if there are existing records, it takes about 20
minutes, the same length of time the sql statement takes to execute
within Oracle SQLPlus.
Am I doing something wrong with my connection, command, or recordset?
How do I make the "execute" method actually execute the sql rather than
just skipping past as it sets up the recordset?

This snippet is not complete, but I think it contains enough to
troubleshoot.

1 Dim cnCIS As New ADODB.Connection
2 Dim cmCIS As New ADODB.Command
3 Dim sConn As String
4 sConn = "Provider=OraOLEDB.Oracle;"
5 sConn = sConn & "User ID=***;Password=***;"
6 sConn = sConn & "Data Source=cisrpt;"
7 sConn = sConn & "FetchSize=100;CacheType=Memory;"
8 cnCIS.Open sConn
9 cmCIS.CommandText = sql
10 cmCIS.CommandType = adCmdText
11 Set rs = cmCIS.Execute(, , adAsyncExecute)
12 Do While (cmCIS.State And adStateExecuting) = adStateExecuting
13 DoEvents
14 Loop
15 If Not rs.EOF Then Range("charges").Value = rs("amount")
16 rs.Close

Line 15 is where my code waits for the database to return the result.
I'm expecting it to execute the sql in line 11 and move on to lines
12-14 while it's waiting for the result.

.

Relevant Pages

  • Re: UPDATE query in Access 2003 raising error
    ... The only reason I questioned the recordset is that you could conceivably ... the SQL looks fine to me. ... Set qdfTemp = db.CreateQueryDef ... Elsewhere in the code I use the same technique to execute an SQL statement ...
    (microsoft.public.access.formscoding)
  • Re: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT I
    ... This statement causes ADO to create a recordset to receive the results ... of the query being executed and assign that recordset to your qryAddProj ... You should use the ExecuteOptions argument of the Execute ... string containing a sql statement to be executed. ...
    (microsoft.public.data.ado)
  • Re: ADODB.Recordset: Operation is not allowed when the object is closed
    ... > ' Create the ADO Connection and Recordset objects. ... > ' Set the connection string, open the connection and execute the ... If your sql string is an insert/update/delete statement, ...
    (microsoft.public.scripting.vbscript)
  • Re: insert Q
    ... many sql statements on the page). ... When you use a connection's Execute method, a Command object is ... Always use an explicit recordset object: ...
    (microsoft.public.inetserver.asp.db)
  • Re: insert Q
    ... >> conn.execute SQL ... When you use a connection's Execute method, a Command object is ... Always use an explicit recordset object: ...
    (microsoft.public.inetserver.asp.db)