Capture an error message from SQL server in an MS Access form?

[Meltdown]

Hi all,

How do I capture an error message from SQL server in an MS Access form?

I have a trigger on a table in sql server 2005, the user is only allowed to update columns if the locked column has not been set to true.

I'm getting an error message back from Sql server when I try to update the form, but it's a bit to cryptic, so i need to capture the error and write my own handler.

I've tried Before and After update events, but they don't seem to capture the sql server odbc error being returned.

Anyone know how to handle this?

Thanks

 

[SQL_Hell]

What version of sql server are you using?

and how is the update occuring? stored procedure?

 

[Meltdown]

hi SQL_Hell,

I'm using sql server 2005.

It's just a normal access query, however I have a fairly complex validation trigger on the table that either accepts or rejects the update.

It's my trigger that is sending back the 'update error' message.

 

[SQL_Hell]

ok, and how is the update occuring?

 

[Meltdown]

The update is happening from the 'inserted' virtual table that is available to the 'instead of' trigger.

 

[SQL_Hell]

Ok understood, lol @ the post editing and lack of reading from both sides

Can you post the sql of your trigger? I need to understand what's going on because I suspect this can be handled a lot better through VBA.

 

[SQL_Hell]

I am not sure why you haven't posted your code, but anyway..

You could try putting in a try catch block into your trigger, and see if this gives you better error information. But I still think this is better handled in the front end.

Try catch example

BEGIN TRY
 
--BLAH BLAH DO SOME TRIGGERY STUFF HERE
 
END TRY
 
BEGIN CATCH
 
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
 
END CATCH

But a before / after update event of your form would be better (please note this example is probably not correct syntax wise, but should give you idea of what I mean)

Private Sub txtBox_beforeupdate()
 
IF ME.recordlocked = true then
msgbox "you cannot update this record, would you like to go to the pub instead?", vbyesno
 
else
docmd.runsql 
 
End Sub

 

[Meltdown]

Hi SQL_Hell, thanks for the reply & code

I'd prefer not to post the trigger code in a public forum.

I'm not having a problem with the trigger, it's firing and returning an error to 'odbc sql update' error, my problem is in capturing that error.

The problem with form level permissions is that you then have to implement security for every form that ever accesses the data in the table. Setting security/permissions at the table level means it's only set up once.

Have you ever managed to capture/handle a sql error returned to an access form?

Regards
Melt

 

[SQL_Hell]

Ok, not sure why you think posting the trigger code is a problem? But you can send it to me in a private message if you like.

I still think this is better done in the forms, and form permissions have nothing to do with anything I posted earlier. How many forms do you have? it can't be that many. It strikes me your want a quick easy solution rather than do the job well....

However the best way to capture errors through triggers is to use add custom errors to the sys.messages table using sp_addmessage
Then force the error using RAISERROR in your trigger.

Check BOL for this

 

[Meltdown]

Hi SQL_Hell,

Thanks for your input, I'll look at a forms solution instead.

Regards
Melt