Identifying DB-Errors

[Rolf Gossen]

Hello NG,

i'm facing the following problem. I have got an SQLServer Table
tblAuthors with to Restrictions Primary Key PK_tblAuthors and a Unique
Key
IX_tblAuthors. The data in this table is updated via an endless
Access-Adp Form.

The problem is now that as soon as one of the Restrictions is violated
I can catch the OnError Event in the Form_Error(DataErr As Integer,
Response As Integer) method. Now I would like to offer the User a more
telling Error-Message than
"Violation of UNIQUE Key IX_tblAuthor. ..."
or
"Violation of PRIMARY Key PK_tblAuthor ..."

The problem is just that both Errors have the same error-Number (that
is 2627) and consequently have the same error-Message in
err.Description.

So how can I find out which of the two errors occured, in order to
display a customized error message.

Thanks for your help
Rolf

 

[Steve Jorgensen]

Hello NG,

i'm facing the following problem. I have got an SQLServer Table
tblAuthors with to Restrictions Primary Key PK_tblAuthors and a Unique
Key
IX_tblAuthors. The data in this table is updated via an endless
Access-Adp Form.

The problem is now that as soon as one of the Restrictions is violated
I can catch the OnError Event in the Form_Error(DataErr As Integer,
Response As Integer) method. Now I would like to offer the User a more
telling Error-Message than
"Violation of UNIQUE Key IX_tblAuthor. ..."
or
"Violation of PRIMARY Key PK_tblAuthor ..."

The problem is just that both Errors have the same error-Number (that
is 2627) and consequently have the same error-Message in
err.Description.

So how can I find out which of the two errors occured, in order to
display a customized error message.

Thanks for your help
Rolf

You need to get the Description property of the first error in the Connection
object's Errors collection.

 

[Rolf Gossen]

You need to get the Description property of the first error in the Connection

object's Errors collection.

Thank you, but how do I get this (central) Connection object?

Rolf

 

[Rolf Gossen]

You need to get the Description property of the first error in the Connection

object's Errors collection.

Sorry, to be a little bit more precise.
the Connection Objects error Collection has no Item since checking
CurrentProject.Connection.Errors.count always leads to 0.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Dim erro As Error

' Index-Verletzung IX_tblRelation_Location_Haulier
If DataErr = 2627 Then
If CurrentProject.Connection.Errors.count > 0 Then
Set erro = CurrentProject.Connection.Errors(0)

If (InStr(1, erro.Description, "IX_tblAuthors" Then
MsgBox ("Customized IX-Error Msg")
Response = acDataErrContinue
ElseIf (InStr(1, erro.Description, "PK_tblAuthors") > 0)
Then
MsgBox ("Customized PK-Error Msg")
Response = acDataErrContinue
Else
Response = acDataErrDisplay
End If
Else
' ALWAYS STEPS HERE >>>> (((
Response = acDataErrDisplay
End If
Else
Response = acDataErrDisplay
End If
End Sub

thank you
Rolf

 

[Steve Jorgensen]

Thank you, but how do I get this (central) Connection object?

Rolf

Sorry, I didn't read closely enough. You're using a form. Unfortunately,
Access makes the error info really, really hard to get to in this case. About
the easiest way I know is to not use a bound form at all, but use an unbound
from instead. Capturing the errors with a bound form is almost impossible in
an MDB, and I don't think it's possible at all with an ADP since most things
you try to do to an ADP form's recordset while it's bound the the form end up
corrupting Access and causing it to crash.

Sorry I don't have better news.