Connection string - Query SQL Server with ADO

[arno]

Hello,

what is the "best" method to query a MS SQL Server with Excel and ADO?

These are the options:

1) SQL native Client (ODBC)
2) SQL Server (ODBC)
3) Microsoft OLE DB Provider for SQL Server
4) .Net Providers (but I could not find a free one)

I have been using 3), however, I get a timeout after 30 sec with complicated
queries and I cannot find the reason for this. (I tested and did not get
timeouts with 2). There are no timeouts set to 30 on the SQL-Server, the
connection string etc.

regards

arno

 

[arno]

3) Microsoft OLE DB Provider for SQL Server

timeouts 30 on the SQL-Server, the

the CommandTimeout property has to be set, otherwise the default of 30sec
will be used

Set cn = New ADODB.Connection
cn.CommandTimeout = 0 '0 = infinite, anything else are seconds

arno