Hi,
You mentioned that you are using ADO in VBA to pass the command to execute the SP on the Server.
the time out issue is due to the command timing out on the server (This is a setting that has to be configured).
to resolve this, when you are creating your command object to pass to the server, mention the CommandTimeout value too over there.
For Example(Example Code that works) in the VBA module of Access (This is only in case of Access ADP file) :
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim RstOut As ADODB.Recordset If in case your SP returns any records then creat a recordsect object to hold them
Set RstOut = New ADODB.Recordset
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = [Name of your procedure]
cmd.CommandType = adCmdStoredProc
cmd.CommandTimeout = 0 <- mention your time out value/setting here, 0 means no time-out
Incase if you are passing Input parameters to your SP
Set prm = New ADODB.Parameter
prm.Type = adChar Set Parameter type
prm.Size = Len([parameter]) size of your parameter(Length of it in case of String)
prm.Direction = adParamInput
prm.Value = [parameter] value of the parameter
cmd.Parameters.Append prm append it to the command object to be passed to the server
Finally call/execute the Stored Procedure present on the server, assign the returned records if any, to the recordset object
Set RstOut = cmd.Execute()
After you are done, also remember to
Set RstOut = Nothing
Set cmd = Nothing
Set prm = Nothing