VBA / ADODB运行时错误：3704 [英] VBA/ADODB Run-Time Error: 3704

问题描述

以下VBA子程序将运行大多数查询。 （即： 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; _ 
& 数据源= xxx / xxx; 
 out QueryString 
 
 rs.Open QueryString，connStr，adOpenStatic，adLockOptimistic 
 Range（DataTable）。清除
单元格（1，1）.CopyFromRecordset rs 
 End Sub 
  

但是，当我运行下面的查询时，会立即弹出以下错误消息：运行时错误'3704'：对象关闭时不允许操作。

  with all_hours as 
（select to_date（'2009-11-03 05:00 PM'，'yyyy-mm-dd hh：mi PM'）+ numtodsinterval（level-1，'hour '）小时
从双
连接级别< = 4 / *小时* / 
 
）
选择h.hour 
，count（case当h.hour = trunc（s.sampled_on，'hh24'）然后1结束）采样
，count（h.hour = trunc（s.received_on，'hh24'）然后1结束的情况）收到
，count（h.hour = trunc（s.completed_on，'hh24'）then 1 end）的情况下完成
，count（h.hour = trunc（s.authorized_on，hh 24'）然后1结束）授权
从all_hours h交叉连接样本s 
组由h.hour 
  

为什么？

解决方案

我刚刚重构了我的查询（链接），这样我就可以看出来了。然后，我在Excel中创建一个连接以查看名称。

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.

这篇关于VBA / ADODB运行时错误：3704的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！