不同版本库中的VBA ADO连接超时 [英] VBA ADO connection timeout in different version of libraries

问题描述

我正在尝试使用VBA（Excel 2007）和ADO连接对象与SQL Server 2012上的视图进行连接。除了一个视图外，其他所有功能都可以正常运行，即使对于最简单的查询，该视图也会出现运行时错误。要设置断开连接的最大时间，请使用命令 YourConnection.ConnectionTimeout = 0 。 Microsoft ActiveX数据对象x.x库的不同版本（2.0、2.1，...，2.8、6.1、6.2）是否具有不同的最大超时时间，或者全部具有最大30秒？另外-该库的2.0，...，2.8版本之间有什么区别-我在哪里可以找到它？

I'm trying to connect with views on SQL Server 2012 using VBA (Excel 2007) and ADO connection object. Everything works perfect except one view, for which Runtime error occours even for simplest queries. To set maximum time out of connection I use command YourConnection.ConnectionTimeout = 0. Does different versions of Microsoft ActiveX Data Objects x.x Library (2.0, 2.1,...,2.8,6.1,6.2) have different maximum timeouts, or all have 30 secunds maximum ? In addition - what are difference between 2.0,...,2.8 versions of that library - where could I find it out ?

推荐答案

ConnectionTimeout仅适用于连接建立。如果查询在执行期间超时，则应将Command对象与CommandTimeout结合使用。注意，您必须在每个Command对象上进行设置，因为它不是从Connection对象继承的。

ConnectionTimeout only applies to connection establishing. If your queries timeout during execution, you should use Command object with CommandTimeout instead. Beware, you have to set it on each Command object as it is not inherited from Connection object:

Dim cnn As New ADODB.Connection
cnn.ConnectionString = "Provider=SQLOLEDB;Data Source=xxx;Catalog=xxx;User ID=xxx;Password=xxx;"
cnn.Open

Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = cnn
cmd.CommandTimeout = 120
cmd.CommandText = "SELECT * FROM [Table]"

Dim rs As New ADODB.RecordSet
rs.Open cmd, , adOpenStatic, adLockOptimistic

检查 MSDN 了解更多信息和 MDAC / ADO历史记录。

这篇关于不同版本库中的VBA ADO连接超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！