Is there any way of trapping SQL Server error messages in Access and replace it with a more User Friendly easier to understand message? I have attached an example picture of what I am trying to trap.

Yes, add an error handling code block:


Sub Testing()
On Error GoTo errhandle
Call openConnection
Objconn.Execute "sptest"
Call closeConnection
errhandle:
If Err.Number = -2147217900 Then
MsgBox "This value already exists in the database and cannot be added."
Else
MsgBox "An unexpected error has occurred please forward the following information to tech support" & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error description: " & Err.Description
End If
End Sub


In this case I've got two procedures openConnection and close connection that do just that.

I then execute a storedprocedure that attempts to insert a duplicate record, this generates an error and steps the code into errhandle:
If the error number matches the value for a unique key violation (debug.print it to be sure, I've included the error number I get) then you'll get a specific error message otherwise you get the generic message that is still likely to be a bit friendlier than the default.

You can also use RAISERROR in your stored procedures to create a custom error message at the server side, BOL has plenty of info here.