Translating SQL Server error messages into plain english
-
I'm looking for a means to capture error messages coming from SQL server e.g.
I'd like to replace this message with something that a business user would understand like; "You need to supply a value for each mandatory field before you can save this record."
I'm using MS Access 2010 and SQL Server 2016
which I couldn't figure out how to make work (maybe I'm just missing something).
which, again, I couldn't make work - more so couldn't figure out how to make it work or what it is doing (e.g. me.show 1 and the cmdOK_Click subroutine) or where to put the code. Additionally, I don't think it will do what I want. The ability to translate the messages into something user-understandable doesn't seem to be there
- https://support.office.com/en-ie/article/cannot-trap-specific-odbc-errors-in-onerror-property-of-a-form-kb-206175-706122ff-24a3-4e41-ad59-3a7729a2c3d7
which looks like it should work. I desperately want for this to work because it looks like it would enable me to customize my messages, but I can't make it go to the error handling part of the code. This example is straight from Microsoft!
This appears to be relying on using Windows OS calls, hijacking the error message shown above and overwriting the text with a new message. Yikes!
Is what I'm wanting to do really that hard?
Thanks in advance for any assistance you can provide.
-
I would consider calling a stored procedure. In the stored procedure, have a BEGIN TRY logic. In the CATCH block, feel free to translate the message any which way you want
-
Are your users getting those kind of ODBC errors from a form where they are adding/editing data on a form? Or are they adding or editing data at the table or query level If it's the latter, best practices suggest using a form in all cases where additions or edits are allowed. On forms, you can set properties to prevent the kind of editing your error message describes. And at the form level, you can customize the error messages so they make sense to the user. Hope this helps - if it doesn't, post back with more info.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain! -
Instead of trapping the error when it reaches the database, you should consider adding form-level validation. For example if your field MyText is required, in your OK button click you might do this:
with me
if isnull(.MyText) then
msgbox "MyText is a required field"
else
' process the OK button click
end if - WILLIAM MITCHELL - Friday, March 22, 2019 10:41 AM
oops forgot the last line
-
Wendell; thanks for your response. To answer your question; users are using a form. The errors mostly occur when a user navigates (accidentally tabbing from the last field onto the next row or clicking back on the main from) off of a from (Subfrom actually) without filling in all of the mandatory fields. Can you tell me what properties of a form I would set? Is it the approach that William suggests.
William; thank you as well, for your response. I have considered duplicating all of the database validation and if that's the way it has to be then that's what I'll do but to me that seems like a fail -effectively duplicating code. I can't believe it's this difficult to trap and translate error messages coming from the server.
-
Sorry for the delay in responding. And yes, it is essentially the sort of code that William suggested. It sounds as though you are dealing with a continuous form that is a subform on a main form. There is a BeforeUpdate form event that is triggered each time a record is changed (there is also a BeforeInsert event), and you can put code on it to check each field that requires data being entered. In addition, you can put a constraint on a text box that says an entry must be made, so if a user tabs through a text box leaving it null, the form will stop and require an entry before moving to the next text box. That doesn't require any code, although you can also put code behind each text box to do checking. Hope this helps.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain! -
Wendell;
Thanks again for your response. You've helped me in the past and I appreciate it.I'm shocked/dismayed/disappointed that there isn't a better/simpler way to leverage the error reporting coming from SQL Server and that the answer seems to be to effectively preempt that validation by duplicating it in MS Access.
-
If you don't perform validations on data before you attempt to insert it into SQL you will be shocked/dismayed/disappointed your whole life as a developer.
- Chris Hurlbut - Thursday, March 28, 2019 9:11 AM
+1
- CanuckBuck - Thursday, March 28, 2019 6:50 AM
Actually, the issue comes in large part from the ODBC driver, which "tries" to convert the SQL Server message and doesn't do very well. It is possible to write some error handling code to convert the message to something more useful, but it is quite a complex bit of coding to deal with all the potential error cases. Doing it on the form is a relatively simple exercise, and would be desirable even if you were using local Access tables, as the raw error messages from Access aren't always helpful to the end user. Access is a powerful front-end to SQL Server, where local or in the cloud (SQL Azure) and it allows the developer to quickly implement a verry sophisticated user interface. Of course its requirement that you have Access licenses for each user is a downside, and there are others such as needing a PC to run the interface on.
I should note that there are several other forums where there is much greater activity on Access that you will likely get quicker responses. Check out Utter Access, Eileen's Lounge, Windows Secrets Lounge, AccessForums.net and several others noted at MS Access Links. You might also find the FMS Tutorials useful in understanding the issues and advantages of a SQL Server back-end. Hope this is helpful.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply