vba odbc 3704 mysql_VBA / ADODB运行时错误：3704

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?

解决方案

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.