VBA/ADODB Run-Time Error: 3704

Question

The following VBA subroutine will run most queries just fine. (ie: SELECT * FROM DUAL)

Sub DisplayQuery(QueryString As String)
  Dim rs As New ADODB.Recordset
  Dim connStr As String
  connStr = _
   "Provider=MSDAORA.1;" _
   & "User ID=abc;Password=123;" _
   & "Data Source=xxx/xxx;"
  out QueryString

  rs.Open QueryString, connStr, adOpenStatic, adLockOptimistic
  Range("DataTable").Clear
  Cells(1, 1).CopyFromRecordset rs
End Sub

However, when I run the query below, the following error message immediately pops up: Run-time error '3704':Operation is not allowed when the object is closed.

with all_hours as
  ( select to_date('2009-11-03 05:00 PM','yyyy-mm-dd hh:mi PM') + numtodsinterval(level-1,'hour') hour
      from dual
   connect by level <= 4 /*hours*/

  )
  select h.hour
       , count(case when h.hour = trunc(s.sampled_on,'hh24') then 1 end) sampled
       , count(case when h.hour = trunc(s.received_on,'hh24') then 1 end) received
       , count(case when h.hour = trunc(s.completed_on,'hh24') then 1 end) completed
       , count(case when h.hour = trunc(s.authorized_on,'hh24') then 1 end) authorized
    from all_hours h cross join sample s
   group by h.hour

Why?

Hightest

Oracle databases do clever stuff with memory management / connection pooling etc. Connection object can appear closed if query execution is in a queue. A get-around could be (a) query optimisation and (b) pass-through queries. – Langouste

Are there any null values in h.hour? – Govern

or the fields from the sample table? – Govern

The query cannot have any null values by design. However, many columns (including the ones referenced) could be null, however in that case the sample would not be included in the 'count(case when ...)' – Hightest

Can you try to re-jig the query to factor in the nulls? e.g. use IsNull to return 0 instead of relying on the SQL to handle them implicitly? I've had some odd results running SQL through Excel, especially on correlated subqueries. – Govern

Read the error message! If it was a problem with NULL you wouldn't get 'Operation is not allowed when the object is closed.' – Clueless

Answer 1

I just restructured my query (link), so that I could put it into a view. I then created a connection in Excel to view name.

Hightest

Answer 2

If I recall correctly, the ADO Command object has a default timeout (30 seconds, I think) which may be causing your problem: there should be a setting like

cn.ConnectionTimeout = (your value here)

which you could extend.

Thunderous

Neither adding a ConnectionTimeout or CommandTimeout solves this problem. – Hightest

A timeout wouldn't give that error. It would give a "timed out" error. – Wheelock