Access -> ODBC -> SQL Server connection hangs

I have an MS Access 2003 database that uses a SQL Server 2000 database as
it's back-end. The database runs well most of the time but if the users dont
use the database for a period of time (approx 10 minutes) and return to it
then the connection hangs. When they try to perform any action that requires
a connection to the SQL Server database then the egg-timer mouse pointer
displays and the database stops responding. It's as if the connection times
out when it becomes inactive for a period of time. Due to a shortage of time
and the impatience of the operators I haven't seen the error message yet
(assuming one eventually appears). The problem is "fixed" by closing the
database and re-opening it again.
If the operators keep using the database constantly then the connection
seems to stay open and the database operates reliably.
I use an ODBC connection using SQL Server authentication. I don't use
Windows NT authentication because I was getting "Cannot generate SSPI
context" errors when I tried to use it (could this be related to the other
problem?).
Any ideas what is wrong and how I can fix it?
Access 2003/Jet 4.0 has an ODBC setting called ConnectionTimeout which is
"The number of seconds a cached connection can remain idle before timing
out. The default is 600 (values are of type REG_DWORD)." 600 seconds is 10
minutes.
This property is found at
\HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Eng ines\ODBC
On the client where Access is installed, set this to a higher value or 0
for indefinite.
-- james
***Disclaimer: This posting is provided "as is" with no warranties and
confers no rights.***
--
>From: "Davey" <davey@.hello.com>
>Newsgroups:
comp.databases.ms-access,microsoft.public.sqlserver.connect,microsof t.public
.sqlserver.odbc,microsoft.public.sqlserver.server
>Subject: Access -> ODBC -> SQL Server connection hangs
>Date: Tue, 22 Feb 2005 18:21:15 +0100
>Lines: 22
>Message-ID: <381bg7F5hs90lU1@.individual.net>
>X-Trace: individual.net hlPan2h158ALGlSJ0qH+wApbb7gzleZkv9TQYLUPSiELWze4U=
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
>X-RFC2646: Format=Flowed; Original
>Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSF TNGP08.phx.gbl!newsfeed00.
sul.t-online.de!newsfeed01.sul.t-online.de!t-online.de!fu-berlin.de!uni-berl
in.de!individual.net!not-for-mail
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.odbc:43324
microsoft.public.sqlserver.server:378990
microsoft.public.sqlserver.connect:44465
>X-Tomcat-NG: microsoft.public.sqlserver.connect
>I have an MS Access 2003 database that uses a SQL Server 2000 database as
>it's back-end. The database runs well most of the time but if the users
dont
>use the database for a period of time (approx 10 minutes) and return to it
>then the connection hangs. When they try to perform any action that
requires
>a connection to the SQL Server database then the egg-timer mouse pointer
>displays and the database stops responding. It's as if the connection
times
>out when it becomes inactive for a period of time. Due to a shortage of
time
>and the impatience of the operators I haven't seen the error message yet
>(assuming one eventually appears). The problem is "fixed" by closing the
>database and re-opening it again.
>If the operators keep using the database constantly then the connection
>seems to stay open and the database operates reliably.
>I use an ODBC connection using SQL Server authentication. I don't use
>Windows NT authentication because I was getting "Cannot generate SSPI
>context" errors when I tried to use it (could this be related to the other
>problem?).
>Any ideas what is wrong and how I can fix it?
>
>
|||How are you connecting, linked tables via DSN or by supplying
connection string in code? (the latter is recommended). You can also
try sticking a SQL Profiler trace to see what's going on at the
server, and try connecting from user's machines using some other
client besides Access.
--Mary
On Tue, 22 Feb 2005 18:21:15 +0100, "Davey" <davey@.hello.com> wrote:

>I have an MS Access 2003 database that uses a SQL Server 2000 database as
>it's back-end. The database runs well most of the time but if the users dont
>use the database for a period of time (approx 10 minutes) and return to it
>then the connection hangs. When they try to perform any action that requires
>a connection to the SQL Server database then the egg-timer mouse pointer
>displays and the database stops responding. It's as if the connection times
>out when it becomes inactive for a period of time. Due to a shortage of time
>and the impatience of the operators I haven't seen the error message yet
>(assuming one eventually appears). The problem is "fixed" by closing the
>database and re-opening it again.
>If the operators keep using the database constantly then the connection
>seems to stay open and the database operates reliably.
>I use an ODBC connection using SQL Server authentication. I don't use
>Windows NT authentication because I was getting "Cannot generate SSPI
>context" errors when I tried to use it (could this be related to the other
>problem?).
>Any ideas what is wrong and how I can fix it?
>