In MS Access 2003 my tables are linked via a SQL Server.
I'd like to change the default long-winded 'ODBC etc etc 'error message when a duplicate PK is added to a table to a more user friendly 'plain english' message.
Can this be done is Access, VBA or even on the SQL server? (which I have acces to).
From an Access point of view, you might well be better off trapping the Error in VBA when you call your StoredProcedure. You can have your Stored Procedure return "custom" Error messages using RAISEERROR, but you should still trap and handle them properly in the VBA.
very brief sort of example
Code:
On error goto errhandle
exec spinsertrecord "some data" -- causing the error
errhandle:
If err.number = Whatever the number is for "you tried to insert a duplicate" Then
msgbox "you tried to enter a duplicate record"
else
msgbox "something else went wrong: " & err.number & vbcrlf & err.message
endif
exit sub
On error goto errhandle determines what VBA should do when it encounters an error, in this case I want it to goto errhandle which contains some code do something with the error rather than stick the default message to screen (on error goto 0) or ignore it (on error resume next).
errhandle:: This is the start of your error handling block it goes in your sub(), normally at the bottom of your code.
Code:
If err.number = Whatever the number is for "you tried to insert a duplicate" Then
msgbox "you tried to enter a duplicate record"
else
msgbox "something else went wrong: " & err.number & vbcrlf & err.message
endif
exit sub
This bit determines what you do with an error. Every error has a number (err.number) and default text (err.description). The IF block checks for the error number returned by SQL server/ODBC (don't know what it is off the top of my head) and if it's the duplicate row/value error then it displays a more friendly message, otherwise it displays the standard err.number and what should be err.description which is the standard, normally cryptic error message and the standard option to end or debug the code.
The exit sub then tells it to exit the sub() you're currently in although you could tell it to Resume next if you're sure you just want to carry on.
So you'd normally have something along the lines of:
Code:
Sub example()
ON ERROR GOTO errhandle
[i]some vba[/i]
errhandle:
IF err.number = [i]the error you want to handle[/i] then
msgbox "You tried to enter a Duplicate value"
Resume next ' I'm going to let the sub carry on here, maybe it's not an important error, the Code will go to the next line in [i]some vba[/i] above and try to carry on.
else
Msgbx "something else bad that I don't really care about happened"
Exit Sub ' But here I'm going to terminate the code
Endif
end sub
You can be quite selective in how you handle your errors. You might only want to go to errhandle when a duplicate row is inserted and present a nicer message to the user and leave VBA to do the rest.
The error message is not the usual 'MS Access debug' type, it's an 'ODBC Error dialog box'. The err number is 3146 'ODBC Call Failed' but it's still not trapping it.
Sub example()
On Error GoTo errhandle
errhandle:
If Err.Number = 3146 Then
MsgBox "You tried to enter a Duplicate value"
Resume Next
Else
Msgbx "something else bad that I don't really care about happened"
Exit Sub ' But here I'm going to terminate the code
End If
End Sub
I'm putting this in the form's general procedures.
OK, to be "extra sure" assign err.number to a variable immediately after your insert command/Stored Procedure triggers. If I get a few more minutes today I'll try and replicate the problem, you calling a command directly to the server or just locally inserting to the linked table in Access?
Code:
'your insert statement with a duplicate value to cause the error to occur
Command.execute
'now we immediately assign the error number to a variable
intErr = err.number
debug.print intErr
I'm just using a standard MS Access form and no SQL or vb code to to the INSERT.
I'm just relying in the built in mechanisms which is where i'm having a problem to capture the error.
I am comfortable using SQL server and TRIGGERS etc but this custom message box is only necassary for the MS access front end form.
OK, that's probably why you're having problems trapping the error.
You might be better off having the user populate the information on the form and hit a "Submit" button, or similar with VBA code attached to that button triggering the INSERT and able to handle any errors.