Connection Timeout

[Guy]

Hi All,

Using the standard Data Link Properties Connection dialog there is option on
the Advanced Tab to change the connection timeout from the default 30 seconds
to another value.

Occasionally, depending on the database workload and volume of data, I have
reports that timeout executing their record source queries so generally I set
the connection timeout to greater than 30 seconds.

This works fine when I am developing, but in the production environment
because I use my own Logon Dialog I always remove any saved connection
information on application exit:

Application.CurrentProject.OpenConnection ""

to prevent the standard microsoft logon dialog appearing. However, this also
removes the connection timeout property that I have set via the Advanced tab.

I have reviewed the properties of the standard connection string I use on
application startup:

CurrentProject.OpenConnection ";User ID=" & USER & ";Password=" & PWD etc...

But there doesn't appear to be a property to change the connection timeout.

Obviously Microsoft's standard connection dialog does this somehow, does
anyone know how? (the SQL Profile didn't reveal anything)

Your assistance appreciated.
Guy

ps. I realise that I could revise the report queries so that those that may
take longer to execute than the default timeout are not called via the report
recordsource property (eg. use the adodb.command object with the command
timeout extended) but thought I would see if there was a simpler solution
first.

 

[Sylvain Lafontaine]

The default value for the connection time-out should be 15 seconds, should
limit the maximum time that the login process for etablishing a connection
with the SQL-Server can take and shouldn't have any impact on the execution
of a query command.

Try setting the General Time-Out on the All tab (set it to 0 to have
infinity) or the OLE/DDE time-out in the Advanced tab for the Options.

Another possibility would be to create your own recordsets. This way, you
can specify the command time-out explicitely. See the post from Greg Snidow
on 2007-01-19 about "Trying to learn ADO" for an example of this.

 

[Guy]

Thanks for your input,

I have looked at the Refresh Interval and DDE/OLE timeouts in the past and
my understanding, from reading various knowledge base articles on these
settings, is that they don't have any impact in an Access Project.

Further more, if I understand your post correctly it appears that the
connection timeout setting on the advanced tab of the connection dialog also
doesn't impact on recordsource query timeout, but only login connection
timeout. However, there is no doubt that some of my report recordsource
queries are timing out (they fail quitely) because when I convert them to be
called via an ADODB.Command object with and extended command timeout they
work fine.

Is there no way to extend recordsource query timeouts?

Your assistance appreciated

Guy

 

[Guy]

Sylvain,

Where can I find the "All" tab and General time-out?

Guy

 

[Sylvain Lafontaine]

The All tab is located beside the Advanced tab on the File | Connection
dialog window.

The Refresh Interval has nothing to do with queries or actions timeouts;
however, it was my understanding that the DDE/OLE timeout was important. I
might be wrong but it should be easy for you to test this option.

The connection timeout and the extended command timeout are two differents
things. The connection timeout is used for etablishing a connection while
the command timeout for the connection object and the extended command
timeout for the command object are used for limiting the amount of time a
query or an action on the SQL-Server can take. If you don't specify its
value, the extended command timeout will inherit the value of the command
timeout of the connection object.

Increasing the connection timeout shouldn't have any affect excerpt for the
cases where either the network or the SQL-Server is/are overloaded.

I might be wrong but it should be fairly easy for you to test for these
options.