ADODB Timeout

I'm trying to use the following ADODB connection string to retrieve a dozen
records from a MS SQL Server database. Everytime I run the routine, I get a
Timeout Expired error even though I'm using the ConnectionTimeout = 0 in my
connection string.

Any thoughts as to why this is happening?

TIA

Mark


Private Sub Test()

GetData "11/30/2005"

End Sub

Public Sub GetData(ByVal dtEnd As Date)

Dim Cnxn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strCnxn As String
Dim strSQL As String

' Open connection
strCnxn = "Provider=SQLOLEDB;Data Source=MY_SRVR;Initial
Catalog=MY_DB;User Id=MY_UID;Password=MY_PWD;"
Set Cnxn = New ADODB.Connection
Cnxn.CursorLocation = adUseClient
Cnxn.ConnectionTimeout = 0
Cnxn.Open strCnxn

'SQL to call from db
Set rs = New ADODB.Recordset
strSQL = "SELECT * FROM dbo.fnMetrics('" _
& Format(dtEnd, "mm/dd/yyyy") & "')"

rs.Open strSQL, Cnxn, adOpenStatic, adLockReadOnly, adCmdText

Range("Metrics_Data").CopyFromRecordset rs

rs.Close
Cnxn.Close

End Sub

Never mind I figured it out by adding the following command code. Apparently
the command timeout is the important one, not the connection timeout.

Set cmd = New ADODB.Command
cmd.ActiveConnection = Cnxn
cmd.CommandTimeout = 0
cmd.CommandText = strSQL
cmd.CommandType = adCmdText

Set rs = cmd.Execute




"MChrist" wrote:

> I'm trying to use the following ADODB connection string to retrieve a dozen
> records from a MS SQL Server database. Everytime I run the routine, I get a
> Timeout Expired error even though I'm using the ConnectionTimeout = 0 in my
> connection string.
>
> Any thoughts as to why this is happening?
>
> TIA
>
> Mark
>
>
> Private Sub Test()
>
> GetData "11/30/2005"
>
> End Sub
>
> Public Sub GetData(ByVal dtEnd As Date)
>
> Dim Cnxn As ADODB.Connection
> Dim rs As ADODB.Recordset
> Dim strCnxn As String
> Dim strSQL As String
>
> ' Open connection
> strCnxn = "Provider=SQLOLEDB;Data Source=MY_SRVR;Initial
> Catalog=MY_DB;User Id=MY_UID;Password=MY_PWD;"
> Set Cnxn = New ADODB.Connection
> Cnxn.CursorLocation = adUseClient
> Cnxn.ConnectionTimeout = 0
> Cnxn.Open strCnxn
>
> 'SQL to call from db
> Set rs = New ADODB.Recordset
> strSQL = "SELECT * FROM dbo.fnMetrics('" _
> & Format(dtEnd, "mm/dd/yyyy") & "')"
>
> rs.Open strSQL, Cnxn, adOpenStatic, adLockReadOnly, adCmdText
>
> Range("Metrics_Data").CopyFromRecordset rs
>
> rs.Close
> Cnxn.Close
>
> End Sub