0

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.

  • 1
    This sounds like you're having network issues. Another possibility is the SQL Server connection is timing out. You may want to look at modifying the ODBC connection string to include a longer timeout value. – Max Vernon Apr 16 '14 at 1:18
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.