OK, I've done a simple ADO connection from Access to my SQL server, and
it works for most things. When I try to execute a stored procedure, I
get a timeout error from the server every time. When I run the
procedure from SSMS it only takes 55 seconds. I also have the
Connection timeout set to 0, which is supposed to be infinite. What am
I doing wrong?

Here is the code:

Function LazyUpload(strPublication As String, strPubDate As String) As
String
Dim Cn As New ADODB.Connection

On Error GoTo ERRORCODE
Cn.ConnectionTimeout = 0
Cn.Open ("Provider=sqloledb;" & _
"Data Source=sqldb.rl.int;" & _
"Initial Catalog=Database;" & _
"Integrated Security=SSPI")

Cn.Execute "Exec dbo.GenerateAggregateData @p_Publication = '" +
strPublication + "', @p_PubDate = '" + strPubDate + "'"
LazyUpload = "Data Loaded."
EXITFUNCTION:
Exit Function
ERRORCODE:
LazyUpload = "Data Upload Failed." & vbCr & vbCr & "SQL Error
Count:" & Cn.Errors.Count & vbCr & _
"SQL Error 1:" & Cn.Errors.Item(0).Description & vbCr & "VBA
Error:" & Err.Description & "Timout" & Cn.ConnectionTimeout
Resume EXITFUNCTION
End Function


MS Sql Server LazyDBA home page