Randomly failing SQL Server 2008R2 used by Access 2010 front-end via ODBC

Question

From time to time (sometimes days apart, sometimes several times a day) users of a multi-user Access front-end, which has ODBC connections to a SQL Server back-end, see a variety of problems. Most users have Access Runtime, but a few use full Access. At most 15 users will be active at any one time, and their activity is mostly retrieve/amend/save, working on one record (and associated sub-records) at a time, usually for a minute or two.

The problems can be any of:

• an Access form has #Error in all its bound fields.
• an Access form has #Name? in all its bound fields.
• an Access form has #Field! in all its bound fields.
• front-end error handler reports "[ODBC SQL Server Driver][DBNETLIB]General network error
• front-end error handler reports "[ODBC SQL Server Driver][DBNETLIB]Communication link failure

Sometimes (particularly with the first 3 types) the errors can be eliminated for a while by restarting the instance. I'm not a DBA, but I am the closest that the company has. Any suggestions for possible solutions, or at least some informed diagnostic tactics? TIA.

Answer 1

As Max says it sounds similar to a problem we had under a similar situation, especially for those working remote to the main company network.

Ensure that under the Access database Options Advanced tab that the OLE/DDE timeout is set to something like 300 and not the default of 30.

Also in any VBA requests to the database adjust the ADODB command.CommandTimeout value to a high value of at least the same 300, this will stop early timeouts and lost connections. Since we did this we have had no dropouts, it only uses the extra time as needed so do not worry about system locks from this.