How to restore ODBC connection after failure in MS Access

Question

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?

Solution

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.

Comments

[host.13]

Thanks for answer. Yes, this is very useful information, but first of all i need to understand what exactly solves connection problem with ODBC - is there any special method of application like connection.reconnect or .close then .open. Project has about 180 linked tables so refreshing link each of them will take much time. Is there another way to reconnect?

[user3864563]

Why do you have 180 linked tables? If there are tables with similar columns, you can simply relate those columns with other tables, if I am correct.