none
How to capture SQL Server error via MS Access, after error not generated by DAO VBA code RRS feed

  • Question

  • 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 an old post by Dirk on msdn about getting it... .. 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. <br />But this will still involve the hope that I identify all places in the app that does the update.

    Wednesday, March 16, 2016 6:12 PM

All replies

  • Hi Isaac. Good to see you again. Have you seen this technique by Tom? Hope it helps... Cheers!
    • Edited by .theDBguy Wednesday, March 16, 2016 6:20 PM
    Wednesday, March 16, 2016 6:19 PM
  • As pointed out, you can use the forms error event, and trap the error. The “error” value returned may well not tell your specifics, but it is a workable start to trap the error.

    The link DB Guy has given some additional ideas such as adding a timer, and then gathering additional information about the error, but for starters, likely just trapping the error and knowing it is a server error is a good start.

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    kallal@msn.com

    Wednesday, March 16, 2016 6:29 PM
  • Hi DbGuy - Great to see you again as well.  Hope you are doing good.  Long time no talk.

    Thank you - that article looks pretty interesting, it reinforces my sense of surprise that Microsoft has really not provided a method to gain insight from one to the other and that the hacks are pretty 'out there' - but definitely may be useful to me, and appreciate you pointing me to them.

    Albert, I agree--but what if there are various reasons why an "odbc-update on a linked table failed" occurs?  (Permissions, wrong table name, etc).  I'm wanting to narrow down to one reason, rather than assume that all server update failures are for this specific reason.  After taking the time to write a trigger and create a custom server error, it's a shame to ignore that and just say well, I'll assume that all server errors are because of that cause and I'll report as such to the user. 

    My hope in this whole venture was to create a very specific SQL error -- I assumed, perhaps too optimistically, that there was a more direct way for Access to read SQL's error - I thought maybe a pass-thru query in Access, pointed to the server & database on SQL, with something like select ERROR_MESSAGE(), would do it. 

    But even on select ERROR_MESSAGE(), I had trouble finding documentation of the scope of that function... i.e., how to limit to a specific SQL database (or whether it was or wasn't limited to one, or whether a USE statement would be meaningful).

    Wednesday, March 16, 2016 6:50 PM
  • Not sure why i didn't see this before, but I did notice now documentation that error_message() is only for use in a try/catch block. oh well.  Same with @error, apparently.  So never mind that comment on my part. :|

    Wednesday, March 16, 2016 7:44 PM
  • Hi, Isaac Pisors

    According to your description, this workaround is that use store procedure instead of trigger. In that case declare an output parameter of type integer, and optionally an output parameter to display a message rather than using Raiserror. Once the procedure returns, your VBA code can check to see if the output parameter is > 0.

    >>>Not sure why i didn't see this before, but I did notice now documentation that error_message() is only for use in a try/catch block. oh well.  Same with @error, apparently.  So never mind that comment on my part. :|<<<

    This is the forum to discuss questions and feedback for Access for Developers, so I suggest that you could post your question on forum for SQL Server

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?category=sqlserver

    Thanks for your understanding.
    Thursday, March 17, 2016 7:33 AM
  • "This is the forum to discuss questions and feedback for Access for Developers, so I suggest that you could post your question on forum for SQL Server"

    ... My last comment, "So never mind that comment on my part", disposes of that and I no longer have a question about it.  But that was just one small element of a question that, necessarily, merges Access and SQL Server. 

    It's always difficult when one poses a development question that bridges two technologies. The Access people want the question gone to SQL, the SQL people want the question gone to Access.  In real life we build apps that bridge multiple technologies ... But forum titles don't usually match real life.  :)

    If I understand correctly, you are suggesting the following:

    Instead of a trigger, (AND, instead of the bound Access Form doing the update), a pass-through query executing a stored procedure would be the thing that actually performed the data update.  By doing this I could, of course, integrate return values and then read those values in Access. 

    Right?

    If so, I do agree - but then again, I could probably even more easily keep the bound form method of performing the update, but, add a pass-through query first that would simply look up whether the Freeze was "on" or "off"--and doing so would be faster than my original complaint about doing dlookups [on Access tables].  Instead I would, perhaps, Dlookup() to the pass-through query itself. 

    On the last point, if I understand correctly, I agree this seems like perhaps the best way to go.

    I'd probably leave the trigger on how it is - but no longer for the purpose of raising the custom error, as much as, just a good design principle to protect the data if the Freeze is on and somehow the FE Access App fails to read the Freeze and prevent the update at the app level.

    Friday, March 18, 2016 9:58 PM
  • Hi, Isaac Pisors

    According to your requirement, I am not able to find better workaround than using store procedure instead of trigger. 

    In addition if you have any feedbacks for Access, please feel free to submit them to User Voice:

    https://access.uservoice.com/

    Thanks for your understanding.
    Wednesday, April 13, 2016 8:29 AM
  • David,

    Thanks for your comment.  I understand.

    Quick question - in the future, on issues that seem to include two products (say, Access and SQL Server), how should I approach where to post?

    Hypothetical future product feedback or "how-to":  Imagine if it was a question about SQL column datatypes vs. Access linked table interpretation.  (just an example)

    Wednesday, April 13, 2016 4:09 PM