command timeout in Access 2002 and SQL Server

[Guest]

I have an Access 2002 adp program that uses a very large SQL Server database.
I am running into an issue where the sql commands are timing out when
selecting large recordsets. I have added the following code prior to opening
recordsets:
set cn = currentproject.connection
cn.commandtimeout = 0

This doesn't seem to work for users that are running the .adp file with
Access 2002 runtime.

Any ideas?

 

[Sylvain Lafontaine]

For ADE, I don't know but for ADP, Access is opening three connections (and
sometimes a fourth) when accessing the SQL-Server; with the effect that
setting the currentproject.connection.commandtimeout will only be of limited
use.

You should try setting the Connection TimeOut and the General Timout to 0.
You will find these under the "File | Connection | Advanced" and "File |
Connection | All" tabs.

 

[Guest]

I understand you can set the general settings for an .adp file, but how can I
change it so it works as an .ade file when run using access 2002 runtime?

 

[Sylvain Lafontaine]

Like I said, I don't really know about ADE because I don't use them.
However, you might try setting these values directly in the ADP file before
making the conversion to the ADE file.