Gracefully handle ODBC connection lost failure
Today, when a ODBC connection is lost, we get:
(For SQL server)
A SQL server login screen which (in my opinion) the user should never see in the first place. He has no idea what to with it.
When/if he cancels, he gets an ugly error message.
We need to be able to:
A) Handle that failure gracefully WITHOUT showing the login screen.
A++) Be able to reconnect without having to restart the application.
Anders Ebro (TheSmileyCoder)
shared this idea
This has been fixed and released in recent Access builds.
Thanks,
Michal [MSFT]
38 comments
-
Sigurd Aas
commented
Hi,
Is this fixed related to what connection string is used in Access?(ex: will this work for DRIVER=SQL SERVER or does it require the use of Native SQL Client?)
-
Brian
commented
Specifically, July 2016
I'm not sure why it says that one of the new features is "If Access loses the ODBC connection to an external data source you're trying to use, you'll see an error message describing the failure," when ODBC error messages have been around for years. Maybe they're supposed to be better now?
But if that's the case, why is this one still so easy to get--why doesn't the retry logic do anything to prevent it?
https://support.microsoft.com/en-us/kb/2730109 -
Anders Ebro (TheSmileyCoder)
commented
You can read a bit more about it here:
https://support.office.com/en-us/article/What-s-new-in-Access-2016-76454345-f85d-47af-ace1-98a456cb3496?ui=en-US&rs=en-US&ad=US -
Alphonse Giambrone
commented
Great. Thanks B.
-
B. Clothier
commented
Alphonse, you can trap it using form's Error event, so yes.
-
Brian
commented
Also, what minimum Access 2016 version are we talking about? Is there any other requirement? Just trying to understand why I would get the garden-variety 10054 error with the latest version of Access if something has changed.
-
Alphonse Giambrone
commented
Thanks for the additional information. Among other questions, a big one is: Can the error be trapped, so we can provide our own message?
-
AdminAccess Team
(Admin, Microsoft)
commented
Got it, clarification is in order :-)
Here is some more information on the fix we released.
The fix, improves connection lost experience and allows Access to better handle the failure.
With the fix, in cases that the connection is lost –
• Users are prompted with an error message describing the failure
• The database is in a working state, so users can to continue work on it
• When performing operations that require a connection to the server, Access will attempt to reconnect automatically. If fails, database is still in a working state.we realize this is an area that can improved even further.
Please continue to use UserVoice (new suggestions. it's hard and inefficient to monitor all the comments) to let us know how.Hope that helps,
Michal [MSFT] -
Brian
commented
Maybe more to the point: in reality, is anyone seeing any difference? If not....
-
Alan Cossey Mark 2
commented
Come on, chaps. You told us 6 days ago you implemented this. What have you done, please? We need some details.
-
Alan Cossey Mark 2
commented
If Anders' two items have been covered, then it would be brilliant. One reason we moved away from SQL Azure to MySQL on one project was because the connection kept dropping and we had to keep restarting the Access app, which made it look look very amateurish.
All we need is for Microsoft to tell us what they have actually done.
Earth to Microsoft, come in, please.
-
Alan Cossey Mark 2
commented
Would someone at Microsoft please tell us what is going on. Thanks.
-
Brian
commented
Alan, that was the standard MSI version, not C2R. But I tried C2R anyway, even the Fast ring, which is a later version than yours, and I can still easily reproduce the ODBC 10054 error.
No doubt there are many different kinds of problems relating to ODBC, and perhaps MS is talking about some other. Or there are other steps to take, like using a later version of the ODBC driver that's yet to be released. Etc., etc.
With MS providing zero details, it's all guesswork.
-
Alan Cossey Mark 2
commented
Brian, I've just checked and my Access version is 16.0.7668.2048, which looks substantially later than yours. What update channel are you on? I'm on "First Release for Current Channel (Office Insider Slow)".
-
Anders Ebro (TheSmileyCoder)
commented
That is nice to hear. Is this released for the runtime as well?
-
Brian
commented
I guess we have different definitions of "fixed," because I am using the latest version, 16.0.4456.1003, and that error that I linked to in the post before last is still a thing. We had to retreat from using SQL Azure because of it (it doesn't happen with local SQL).
-
Alphonse Giambrone
commented
Wonderful. Where can we find the documentation on what to expect, where to trap the error, etc.
-
Brian
commented
A couple workarounds for the meantime, which is likely to be a long time:
https://support.microsoft.com/en-us/kb/2730109 -
Mark Burns
commented
@PatrickH:
re: > in the same vein: improve usability for designing forms/reports linked to large odbc datasets. Sometimes it takes ages to do the slightest change to a form when the datasource is a "heavy" query. <this exactly what I requested previously only that the forms DESIGNER should CACHE (optionally) the FIRST RESULT SET (with a manual refresh option) so that network/ODBC query latency DOES NOT affect the form editing experience! The current issue is that if a control is bound to a data source, especially a remote ODBC linked one, then the underlying query can be required to be re-run for EACH and EVERY attempt to make any change to the control (or form)! This can make editing a form/report/control beyond tedious and well into the highly impractical range. so MAKE AN OPTIONAL DATA/RESAULT SET CACHE AVAILABLE for control/form result sets WHEN EDITING a FORM or REPORT!! (come on MS Access Developer Team folks - surely you know how to use ADO disconnected recordsets by now!!)
-
Jeremy
commented
Having to close and relaunch Ms Access when the Internet connection drops is not graceful. Understand that one should have a reliable Internet connection but sometimes, apps are running on a mobile device with wifi and the connection drops. There should be a way to reconnect to an SQL server. I tried compact and repair, .refreshlinks, I tried to drop the tables and reconnect them... nothing works, only closing Ms Access and reopen it would reestablish the connection after Internet connection drops.