Access -> ODBC -> SQL Server connection hangs

[Davey]

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?

[Mary Chipman [MSFT]]

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" <da***@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?