How to restore ODBC connection after failure in MS Access

ms-access odbc database-connection vba reconnect
host.13 picture host.13 · Mar 27, 2013 · Viewed 40.3k times · Source

There is a MS Access application with tables on MS SQL Server linked through ODBC. When connection is lost i receive ODBC error 3146. After connection is restored physically i still receive ODBC 3146 errors. I have to make something like a reconnect to server. How can i do this in MS Access?

Answer

BPCS picture BPCS · Mar 27, 2013

I think all you need to do is refresh the table links. Are you asking how to relink the tables programmatically? Have you tried refreshing the links after being disconnnected and verified that it solves the problem? Refreshing the link in VBA would be something like 

Dim db as Database
Set db = Currentdb()
db.TablesDefs(1).RefreshLink

You may have to loop thru the tables to get the index I am not sure if it will take the table name but you could try it that way first.

Related questions

Does MS Access support "CASE WHEN" clause if connect with ODBC?

Does ODBC support CASE WHEN clause for MS Access? Is there any other database which does not support the CASE WHEN clause? I tried the following query while connecting to MS Access with ODBC but get an exception. SELECT (CASE …

Read More
How to connect PHP with Microsoft Access database

I am currently faced with a new challenge to develop a site using Microsoft Access as the primary database instead of mysql. I have not used MS Access before and I would like guidiance on how to go about it, …

Read More
How to refresh linked tables in an Access mdb when ODBC changes

I can create an Access mdb and add a linked table to an Sql Server database via ODBC. If I change the Sql Server that the ODBC is connecting to with the ODBC control panel applet the mdb still connects …

Read More