Re-Establishing an ODBC Connection once it fails
Posted on 2008-10-05
I have an Access Front End with an SQL Server 2005 Backend. The tables are linked using DSN-less connections
Set Td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, DSNConnectionString)
My Program will be used in a tablet environment where the user will primarily use a wireless connection, but may connect their tablet to a LAN in case the wireless has a bad signal or is experiencing problems.
When the connection is switched from LAN to Wireless or Vise-Versa, the Access Linked Tables lose the ODBC connection, and error with ODBC Connection Failed or ODBC Call Failed. Nothing but closing Access and restarting it fixes it once the error shows. I've tried refreshing the TableDefs, Deleting & readding the TableDefs, Relinking Using the Linked Table Manager and nothing works. I've seen a couple promising posts including something about incrementing a DSN number, but I don't think it applies becuase I'm using DSN-less connections.
Is there any way to trap the error & re-establish the ODBC connection without restarting Access?