Capture SQL Server error (RaiseError) via MS Access, after FORM-based update error

[ipisors]

I have a SQL Server table with a trigger after insert, update, and delete. If a certain condition is found, the trigger rolls back the transaction and raises a custom error using raise error. I've verified it works properly. The basic idea is that during such times as I need to "freeze" all record updating, I will execute a stored procedure that simply places a value in a SQL table that indicates the "freeze" is turned "on". The trigger will look at that, and if turned on, and if SQL current_user is not me or admin, then roll back, raiseerror, etc.



In MS Access, I was hoping to utilize this and only this in order to communicate back to the users. Meaning, for example, I realize I could totally re-structure this method and have MS Access "look up" that value (if freeze is 'on') every time a record is updated... but
1) that would mean identifying a LOT of places where the Access app gives the user a method to update a record, and write dlookup() code all over, and
2) that would mean the slowness of a dlookup, and I don't need to add any slowness to this app.

I wanted to be a "good boy" and handle this on the server side. Admirable, right?? I know.

My problem is trapping the custom SQL error in Access.

I read this: https://social.msdn.microsoft.com/Fo...orum=accessdev

... and tried Dirk's method of using the DAO.Errors collection. Unfortunately what I think I have found out is that that entire method only works if you have actually triggered the error using DAO (vba). (which makes sense). NOT, if an error has occurred that was form-based and not necessarily having used DAO in code.

Does anyone know of a way to trap the SQL server error in MS Access? And not just get "odbc - update on a linked table failed" (or Call Failed, etc etc).

My last option will be to create custom error handling in the Access app that looks up the record in the SQL table to see if the freeze is on, but only does this in an error handling routine that sees if we are getting a cruddy vague "odbc-update..." type of error description in the first place.
But this will still involve the hope that I identify all places in the app that does the update.

[rpeare]

have you tried debug.print err.number & err.description sometimes the same (or similar) descriptions can have multiple numbers
Also, if you are using a form based database you can perform the 'freeze' lookup once, when the main form is opened and never have to check it again, the easiest way to do this is to have a field on your main form that's populated when you open it (dlookup("[switchvalue]", "tablename")), then, assuming the main switchboard stays open while you're in the database you can simply refer to that field using [forms]![frmMainForm]![SwitchValue].

[ipisors]

have you tried debug.print err.number & err.description sometimes the same (or similar) descriptions can have multiple numbers
Also, if you are using a form based database you can perform the 'freeze' lookup once, when the main form is opened and never have to check it again, the easiest way to do this is to have a field on your main form that's populated when you open it (dlookup("[switchvalue]", "tablename")), then, assuming the main switchboard stays open while you're in the database you can simply refer to that field using [forms]![frmMainForm]![SwitchValue].

I'm aware of the err number and description, yes. And that (similar descriptions with multiple or same numbers) is precisely WHY I am posting this question. I don't want to rely on that, I wanted to get the precise error from SQL server.

As for the form comment, that wouldn't work for me. First of all an aside, I never use switchboards (even back when they were available in the old days), and all my access databases are based on a main screen with a tab control and all functionality existing on tabs-with occasional small additional pop up forms as needed.
Anyway, no, I would need to know the immediate instant the Freeze went into effect--checking it once at the beginning of their session wouldn't be sufficient.

[rpeare]

If you're using an ADO connection you can try this:

https://bytes.com/topic/access/answe...r-codes-access

[ipisors]

Yes that's the same as the DAO option for Errors collection. It works, but only if the error was raised as a result of unbound vba code that attempted to perform the action on the SQL table - not solely form-based. Thanks for looking tho I appreciate it.

[rpeare]

this may be going around the deep end, but if you are running this with triggers on the SQL side could you have it write the error to a table then look up any errors from the access side. I don't have anything locally I can experiment with (never used SQL triggers) but it may be another avenue of exploration.

[ipisors]

Yes, agreed. Thanks. I was hoping to avoid adding in a dlookup prior to numerous locations in the FE app where it tries to save.
Sigh.