ODBC Connection Lost – How to Close Database in MS Access

error handlingms accessodbc

I have a Access 2013 frontend linked to a 2014 SQL server. When users leave their workstation with their copy of the frontend open their computer will go often into sleep mode and lose their ODBC connection to the SQL server. When they return their session on the database comes to a violent end causing them to open the task manager and close the application down. Not a pleasant user experience.
How can this situation be trapped? Does something exist that upon return with an ODBC call failure a message box can appear with the button to close file? Ideas?

Best Answer

I think the problem you're trying to solve has been solved for Access 2016, but nothing for Access 2013 (AFAIK). Check What's new in Access 2016 - July 2016:

July 2016

ODBC connection retry logic

When Access loses the ODBC connection to an external data source, users have to close and re-open their Access databases in order to continue working with objects that use data from those external data sources. Access doesn't attempt to re-establish the dropped connection even if the connection is valid again and there is nothing that users can do to make Access try and reconnect to the external data source.

We've improved this experience so that now if Access loses the ODBC connection to an external data source, Access tries to reconnect to the data source automatically. In this scenario, you'll now see the following experience:

If Access loses the ODBC connection to an external data source you're trying to use, you'll see an error message describing the failure.

Access now attempts to reconnect to the data source automatically when performing operations that require a connection to an external data source. If the retry is successful, you can continue working with the data and database objects without having to close and re-open Access.

If the retry fails, you won't be able to work with objects that use the dropped external data sources but you can continue working with other areas of your database that aren't dependent upon the unreachable data sources.

If you attempt to use data or other objects again that rely on the external data connections, Access tries again to reconnect to the external data source.

Related Question