SQL Connection dropped

[Rowan]

I have a Access XP adp with SQL Server 2000 Standard back end. Connections
are made with one of several generic accounts that define different roles /
permissions in the SQL database. This works great but periodically a user
gets a connection error when updating. When this happens they must restart
the front end and log in again to establish the connection. Does anyone
know of timeout issues or dropped connections with this configuration and
how to prevent them? The server has ample resources and the actual number
of simultaneously connections of the applicaiton never exceed 50 for this
app. Could this be related to connection pooling? I have not explictly set
pooling on or off in the connection string so the default behavior.

Possible work around?
I could capture the connection error and reestablish the link to SQL but my
concern is that any data the user has entered but has not been updated in
SQL will be lost or the controls will be refreshed when the connection is
reestablished and thus remove any text in fields that where changed but not
updated.

Thanks,
Rowan

 

[Jerry Boone]

I would use the "currentproject.BaseConnectionString" to base all ADO
connection strings from. If you setup the project to use Windows auth, this
string will authenticate based on the logged in windows user and is very
secure, not to mention easier to deploy to lan clients since all that is
needed is to grant their win login on the sql server.

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz
Secure Hosting and Development Solutions for ASP, ASP.NET, SQL Server, and
Access