I have a stored procedure that takes time to execute.
It is supposed to work properly, but the problem is that it shows the message "Timeout expired" before showing making the results. How can I increase the total Timeout so it will not give me this message?

Thanks in advance.

I'm pretty sure the only way to modify timeout property is to use a separate workspace in DAO, or a separate connection in ADODB. You can't modify the default workspace's or connection's properties as they're always open and won't close.

My code is as follow, please tell me What I should modify or add

Dim db As Database
Dim SQLstr As String

Set db = DBEngine.Workspaces(0).OpenDatabase("", False, False, "ODBC;DSN=BAHRAIN;Description=BAHRAIN;UID=Administr ator;DATABASE=Bahrain;Trusted_Connection=Yes")

SQL = "PlugEmployeesAttendanceReportSourceRosterTmpLeaves"
db.Execute SQL, dbSQLPassThrough

I have to correct myself a bit.

There's a property for QueryDef called ODBCTimeout, which you can set at anytime.

You'd have to create a new passthrough query, put that SP in it, then set the ODBCTimeOut property to whatever, then do a .Execute method upon the querydef instead of db.

Dim qdf As DAO.QueryDef

Set qdf= CurrentDb.QueryDefs("NameOfQuery")

qdf.ODBCTimeout=XX

qdf.Execute

Expanding and adapting on Bannana's fine advice ... The will use a temporary query object .... its "AIR CODE" ...so no garentees!!! ...


Public Sub RunSP()
With CurrentDb.CreateQueryDef()
.Connect = "<Valid ODBC Connect String>"
.SQL = "EXEC PlugEmployeesAttendanceReportSourceRosterTmpLeave s"
.ODBCTimeout = 300 'Seconds
.Execute
End With
End Sub

Mr. dAtarenaline, thanks very much for your continuous help and participations.
When I run this code, it gives me the following error:

Run-time error '3420':

Object invalid or no longer set.

but I am sure that the object (SP) is there. I even tested another object (SP), it gives me the same error.
I dont know what is the problem.

Usually to run a stored procedure, am using the code:

Set db = DBEngine.Workspaces(0).OpenDatabase("", False, False, "ODBC;DSN=BAHRAIN;Description=BAHRAIN;UID=Administr ator;DATABASE=Bahrain;Trusted_Connection=Yes")

SQL = "PlugEmployeesAttendanceReportSourceRosterTmpLeaves"
db.Execute SQL, dbSQLPassThrough

How can I add the timeout to this code

Public Sub RunSP()
Dim db As DAO.Database
Set db = CurrentDB
With Db.CreateQueryDef()
.Connect = "<Valid ODBC Connect String>"
.SQL = "EXEC PlugEmployeesAttendanceReportSourceRosterTmpLeave s"
.ODBCTimeout = 300 'Seconds
.Execute
End With
End Sub

I modify my code as you advise, but the problem still.

Sorry ... I answered to quickly! .... I used this code successfully on a StoredProcedure of mine ...


Public Sub RunSP()
With CurrentDb.CreateQueryDef("")
.Connect = "<valid ODBC connect string>"
.SQL = "EXEC StoredProcedure1"
.ODBCTimeout = 300 'Seconds
.ReturnsRecords = False
.Execute
End With

End Sub


The key is to have a name argument of a Zero Length String. Plus the .ReturnsRecords needs to be set as well, otherwise Access thinks its a Select query and tosses up a error for trying to execute a SELECT ...

Thanks very much it works.
I observed that the MS SQL Server UDFs take the same time taken by the VB functions.
Does the SQL SPs take less time than the UDFs or same if the code algorithm is same?

My SP which calls a UDF called FindLeaveReason for every record of the table.
Is the calling way to the UDF FindLeaveReason from the SP is correct?

ALTER PROCEDURE dbo.PlugEmployeesAttendanceReportSourceNormalTmpLe aves
AS UPDATE dbo.EmployeesAttendanceReportSource_NormalTmp
SET Leave = dbo.FindLeaveReason(Indx, AttDay, Department)