I have multiple Access frontends connected using a DSNLess connection to an SQL Server backend on a domain server. When there is a power cut or the internet fails the front ends lose their connection to the SQL server. This is expected and usually, they will reconnect once the connection is restored. However, often the front end will lose connection to the top n of tables. Sometimes it is just the first table but it can sometimes be multiple tables.
I can relink the tables manually but I don't understand why the links are being lost.
It would be ideal to either avoid this issue or have a way to automatically check all tables are linked and relink where needed using vba.
I have been unable to find any articles that shed light on this issue so any information or tips would be very appreciated.
I am using Access 2013.
Well, even if say the connection broke? I fail to see how this could wind up DELETING the links in the front end. This suggest that some re-link code or some such is failing.
First up, you don't (and SHOULD NOT re-link every time on startup - don't know if you are - but no need - it just makes startup time longer).
Next up:
No question, that a connection break to sql server in MOST cases WILL require you to exit Access. However, if you are now linking and using the native 11-17 driver (I even think native 18 is out), then this newer ODBC driver can recover from disconnections, and you often then not have to re-start Access.
Before the newer and "smarter" odbc drivers, and using the standard "legacy" long time driver (SQL server driver), then any disconnect in fact quite much means you have to shut down access and re-start. No so with the newer drivers - they are much better in this regards.
So, if not yet, then consider upgrading the SQL odbc drivers. The only big downside of the newer drivers is that EVERY work station much ALSO have to install the drivers, and the version you used to link with (say native 17) MUST match and MUST be installed on each work station.
For some of our larger installs, we continue to use the legacy driver. It not as good, and can't recover, but we don't have to install such drivers on each computer.
The legacy SQL server driver has been installed by default since windows XP days, and quite sure that even windows 98SE also had these drivers. So, they are installed at the OS level, and you don't have to thus install these drivers - they simple exist on windows.
But, as noted, check if you are using a later edition of the native driver (17, or 18), since they are able to recover from disconnects. This is especially the case when using a VPN, or cloud based SQL Azure. So, try the newer ODBC drivers if you not already using them.