MS Access front-end/SQL back-end connection issues

[TJBernard]

I have an MS Access front-end with a SQL back-end database. It works great, I connect the MS Access front-end to the SQL back-end using a File DSN.

I have a few users (who are in the database the most) who get ODBC disconnection errors time to time. The cause of the error is not consistent, they seem to just appear out of no where. The table that reports the disconnection is not consistent either.

The only way for the users to resolve the errors is to exit the MS Access database, and then re-enter it.

I have had the DBA team analyze the SQL server, and it does not appear to be having any difficulties. We moved the database to another server (so it would be on a server with less traffic) and I rebuilt the File DSN. But the errors continue.

I will have the network team look into any errors, but I am not certain if they will find anything or not.

Has anyone run into this, and what resolutions did you come up with/work arounds did you perform? I am starting to consider upgrading to a Visual Basic .Net front-end, but wanted to see what options were left.

Thank you for your time.

T.J.

 

[FoFa]

First thing to try is to increase the timeouts in your ODBC connection.
Typically is a network issue but in the past on WinServer 2000 with some applications they have cached writing enabled, and upon occasion it messes up the application and the way it talks to the DB. We have not had the issue with a MSAccess application and Sql Server, but have with other applications (VB, C++, and unknown). But in all cases the DB was on the affected server, but doesn't Access have a system DB where the application resides, or use temp tables, something like that? Disallowing write caching on the server has fixed this in all cases but one which was using Ctree database file system.
We have not had this issue with WinServer 2003.
I don't know if that is your issue or not, just figured I would throw it out.