不同版本库中的 VBA ADO 连接超时
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24142139/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA ADO connection timeout in different version of libraries
提问by Qbik
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 ?
我正在尝试使用 VBA (Excel 2007) 和 ADO 连接对象连接 SQL Server 2012 上的视图。除了一个视图外，一切都完美无缺，即使是最简单的查询也会出现运行时错误。要设置连接的最长时间，我使用 command YourConnection.ConnectionTimeout = 0。不同版本的Microsoft ActiveX Data Objects x.x Library(2.0, 2.1,...,2.8,6.1,6.2) 是否有不同的最大超时时间，或者都有 30 秒的最大值？另外 - 该库的 2.0,...,2.8 版本之间有什么区别 - 我在哪里可以找到它？
回答by grudolf
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:
ConnectionTimeout 仅适用于连接建立。如果您的查询在执行期间超时，您应该使用带有 CommandTimeout 的 Command 对象。请注意，您必须在每个 Command 对象上设置它，因为它不是从 Connection 对象继承的：
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
Check MSDNfor more info and MDAC/ADO history.
查看MSDN了解更多信息和MDAC/ADO 历史。
