command timeout in Access 2002 and SQL Server Doug 13.07.2006 15:10:02 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?

Re: command timeout in Access 2002 and SQL Server "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> 13.07.2006 16:25:52 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. -- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF "Doug" <Doug[ at ]discussions.microsoft.com> wrote in message news:6D1AE575-4E09-4FC1-B42F-B662CEF9A260[ at ]microsoft.com... [Quoted Text] >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?

Re: command timeout in Access 2002 and SQL Server Doug 13.07.2006 17:28:01 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" wrote: [Quoted Text] > 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. > > -- > Sylvain Lafontaine, ing. > MVP - Technologies Virtual-PC > E-mail: http://cerbermail.com/?QugbLEWINF > > > "Doug" <Doug[ at ]discussions.microsoft.com> wrote in message > news:6D1AE575-4E09-4FC1-B42F-B662CEF9A260[ at ]microsoft.com... > >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? > > >

Re: command timeout in Access 2002 and SQL Server "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> 13.07.2006 18:20:47 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. -- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF "Doug" <Doug[ at ]discussions.microsoft.com> wrote in message news:26515463-A210-4EE3-BD95-EC860359C4DA[ at ]microsoft.com... [Quoted Text] >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" wrote: > >> 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. >> >> -- >> Sylvain Lafontaine, ing. >> MVP - Technologies Virtual-PC >> E-mail: http://cerbermail.com/?QugbLEWINF >> >> >> "Doug" <Doug[ at ]discussions.microsoft.com> wrote in message >> news:6D1AE575-4E09-4FC1-B42F-B662CEF9A260[ at ]microsoft.com... >> >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? >> >> >>