M
MChrist
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
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