sql server connect: Access -> ODBC -> SQL Server connection hangs -- sql server connect

Access -> ODBC -> SQL Server connection hangs

Davey
2/22/2005 6:21:15 PM

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

RE: Access -> ODBC -> SQL Server connection hangs

i-jalim NO[at]SPAM online.microsoft.com
2/23/2005 8:03:05 AM

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\Engines\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.***

--------------------
[quoted text, click to view]
comp.databases.ms-access,microsoft.public.sqlserver.connect,microsoft.public
..sqlserver.odbc,microsoft.public.sqlserver.server
[quoted text, click to view]
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.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
[quoted text, click to view]
microsoft.public.sqlserver.server:378990
microsoft.public.sqlserver.connect:44465
[quoted text, click to view]

Re: Access -> ODBC -> SQL Server connection hangs

Mary Chipman [MSFT]
2/23/2005 8:58:00 AM

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

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server connect group.