Timeout Expired

[John]

Hi all,
Whenever I try to run a query or view from ADP connected to a SQL Server, it returned an error of "Timeout Expired". I've tried with changing the timeout paramter to 0 and still have the same problem. Any ideas?

Thanks in advance.

 

[Paul Shapiro]

Here is the code I've used to run lengthy stored procedures. In Access 2000,
I couldn't find any other way to get past the built-in 30 second timeout.
This code let me run arbitrarily long procedures.

CODE:
'Open a new connection to the database
Set conTemp = New ADODB.Connection
With conTemp
.ConnectionString = CurrentProject.BaseConnectionString
.CursorLocation = adUseClient
.CommandTimeout = 200 'allow 200 seconds for commands to execute
.Open
End With

Then use conTemp to execute sql or with an ADO command object:
conTemp.Execute strSQL, , adCmdText
OR
Set cmdLanguageReturnAddress = New ADODB.Command
With cmdLanguageReturnAddress
.CommandType = adCmdStoredProc
.CommandText = "dbo.spu_MailMergeDataStats"
.ActiveConnection = conTemp
.CommandTimeout = 200 'allow 200 seconds for command to operate
.Parameters.Append .CreateParameter("@strViewMailMergeSource",
adVarChar, adParamInput, 128)
.Parameters(0).Value = cstrMailMergeDataTempTableName
Set rstCCOfficeLanguage = .Execute
End With

Avrohom Ephraim Singer later suggested, but I never tested:
If one wants to change the connection timeout for the entire Access-Project
rather than for a specific Query, one can do so by just setting the
CommandTimeout for the CurrentProject's Connection. Thus:
CurrentProject.Connection.CommandTimeout = <nnn>. There's no need to close
and re-open the Project's connection.

Paul Shapiro

Hi all,
Whenever I try to run a query or view from ADP connected to a SQL Server,

it returned an error of "Timeout Expired". I've tried with changing the
timeout paramter to 0 and still have the same problem. Any ideas?

 

[John]

Thanks Paul, I'll try that later.

----- Paul Shapiro wrote: -----

Here is the code I've used to run lengthy stored procedures. In Access 2000,
I couldn't find any other way to get past the built-in 30 second timeout.
This code let me run arbitrarily long procedures.

CODE:
'Open a new connection to the database
Set conTemp = New ADODB.Connection
With conTemp
.ConnectionString = CurrentProject.BaseConnectionString
.CursorLocation = adUseClient
.CommandTimeout = 200 'allow 200 seconds for commands to execute
.Open
End With

Then use conTemp to execute sql or with an ADO command object:
conTemp.Execute strSQL, , adCmdText
OR
Set cmdLanguageReturnAddress = New ADODB.Command
With cmdLanguageReturnAddress
.CommandType = adCmdStoredProc
.CommandText = "dbo.spu_MailMergeDataStats"
.ActiveConnection = conTemp
.CommandTimeout = 200 'allow 200 seconds for command to operate
.Parameters.Append .CreateParameter("@strViewMailMergeSource",
adVarChar, adParamInput, 128)
.Parameters(0).Value = cstrMailMergeDataTempTableName
Set rstCCOfficeLanguage = .Execute
End With

Avrohom Ephraim Singer later suggested, but I never tested:
If one wants to change the connection timeout for the entire Access-Project
rather than for a specific Query, one can do so by just setting the
CommandTimeout for the CurrentProject's Connection. Thus:
CurrentProject.Connection.CommandTimeout = <nnn>. There's no need to close
and re-open the Project's connection.

Paul Shapiro

Hi all,
Whenever I try to run a query or view from ADP connected to a SQL Server,

it returned an error of "Timeout Expired". I've tried with changing the
timeout paramter to 0 and still have the same problem. Any ideas?