VBA/ADODB 运行时错误：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;" _
& "Data Source=xxx/xxx;"
out QueryString
rs.Open QueryString, connStr, adOpenStatic, adLockOptimistic
Range("DataTable").Clear
Cells(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') 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
为什么？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果我没记错的话，ADO Command 对象有一个默认超时（我认为是 30 秒），这可能会导致您的问题：应该有一个
可以延长的设置。
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
which you could extend.
我刚刚重组了我的查询（链接），以便我可以将其放入视图中。然后我在 Excel 中创建了一个连接来查看名称。
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.