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?
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?
>