VBA ADO connection timeout in different version of

问题:

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 ?

回答1:

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

Check MSDN for more info and MDAC/ADO history.