Help with Timeout Expired

[DougStevens]

Created a MS Access project, in MS Access XP. Later found that it needs to be in MS Access 2000. So all my stored procedure that are action queries will not work through code or through a macro.

So I created a function that will run the stored procedure in VBA using a ADO Connection, and the Execute method.

With our small data set, of a few thousand records, it works fine. When we work with actual data, about a million records it gets a timeout.

I've changed the timeout to zero, or even to 300, but it always seems to timeout at about 30 to 45 seconds. I've changed it in the Tools>Options>Advance setting. I've changed it for that particular execution by setting the CommandTimeout property. It still seems to ignore it.

 

[cmmrfrds]

The connection and command objects have default timeouts of, I believe 30 and 15 seconds respectively. If you are using a Command object then set the connection timeout also since the Command must use a connection.

Dim cn as adodb.connection
dim cmd as adodb.command

cn.CommandTimeOut = 300 '- 300 seconds
cmd.CommandTimeOut = 300

 

[DougStevens]

I've tried setting those values to zero and at 300. Even rebooted the computer to ensure that the values took. But still got a timeout around 30 seconds, it was as if those settings were ignored.

I ended up doing the following:
(1) Created a DTS Package to run the stored procedure.
(2) Had the SQL server client installed.
(3) Set a flag, in a table, on to indicate that a package is running.
(4) Ran the DTS Package from the Shell command using dtsrun.exe making sure that the last step in the stored procedure was to turn the flag off.
(5) Had access continously check to see when the flag was turned off to know when the stored procedure had completed.