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