I've searched all over for this issue and have had no luck!
I manage an MS Access front-end that connects to SQL Server 2005 Mirror server, via a DSN ODBC. The Mirror Server database refreshes every 15 minutes. If I perform an operation in my Access database with the ODBC data source that is still running when the SQL refresh occurrs, then the operation will break and I receive Error 3146 "ODBC--call failed". I expect that error to occurr, so I'm fine with that. But the problem is that if I attempt to rerun the operation after this break, the same error occurrs (with a different message, instead of "Communication link failure" it's "General network error"). So, it appears the ODBC connection is permanently broken if only one ODBC call fails.
My question is, how do I restore the ODBC connection within the same Access session? I can restore the connection by closing and re-opening Access, but this is not an acceptable work-around for the automated process. Also, I notice that I'm able to perform the operation again in the same session if I wait 25 minutes, the exact amount of time as the ODBC Refresh Interval; so I tried to set that time to 0 and closed/re-opened Access, but to no avail, I still have to wait 25 minutes (need to reboot PC to take effect?) Note, I also tried using a DSN-less connection and also programatically re-linking the tables, but neither of these worked.