Complicated (?) error handling

[Hugo Kornelis]

Hi,

Sorry for the long message. If you don't want all the details, then just
read the next paragraph and forget about the rest. That's just an
explanation of what I'm trying and why - I welcome any ideas for
improvement, but I really hope to find an answer to this question:

Q: In an Access database (Access 2005 - but answers that apply to all
version from Access 97 are preferred) with linked tables to SQL Server
plus some QueryDef.Execute against same backend, I need to catch only
specific errors. All "standard Access" errors should show the normal
error response as if there's no error handling. All errors induced by
invoking RAISERROR in a SQL Server stored procedure or trigger should
result in the opening of a form. And errors induced by SQL Server but
not through RAISERROR (e.g. constraint violations) should preferably
show the normal response, but I can work aroound it if they also result
in opening the form instead.

Background: I'm working on an Access DB to use as front-end for a SQL
Server DB. I'm using linked tables over ODBC / SQL Native client to
connect Access to SQL Server, and VBA code (QueryDef.SQL = "EXEC MyProc"
/ QueryDef.Execute) to execute stored procedures. And now, I have a
special error handling requirement that I can't figure out how to
implement.

Basically, there are three categories of errors that are relevant:
1) Errors intercepted by Access, e.g. alphabetic info in a numeric field
or entering a value that is not in the list for a listbox.
2) Errors detected by standard constraints in SQL Server, e.g. a NULL
value in a NOT NULL column or a violation of a FOREIGN KEY constraint.
3) Errors detected by custom code, either in the stored procedure or in
a trigger. These are raised by invoking RAISERROR in the SQL Server
code.

Without error handling, the first category shows a clear message,
describing exactly what's wrong. In Dutch, since I'm using a Dutch
version of Access. Great.

The second category shows a mixed-language message. First the Dutch
equivalent of "ODBC: the call has failed", and then (in English, since
SQL Server has no Dutch version) the standard text for the constraint
violation, embedded in lots of extraneous information. For instance:
"ODBC: de oproep is mislukt.
"[Microsoft][SQL Native Client][SQL Server]Cannot insert the value NULL
into column 'xxx', table 'yyy'; column does not allow nulls. INSERT
fails.(#515)[Microsoft][SQL Native Client][SQL Server]The statement has
been terminated.(#3621)"
This is not ideal, but it is acceptable. Since the number of constraints
is limited, I could also add VBA code to check all constraints and show
custom error messages before sending modifications to SQL Server.

But it's the third category that's causing me grief. If a trigger or
stored procedure catches an error, the modification is disallowed, a row
is entered into a logging table with timestamp, userid, and reason for
the error, and RAISERROR is used to display the error message and raise
an error condition. How Access shows this depends on the source of the
problem. If data modification in a linked table causes a trigger to
throw an error, the output I see consists of Dutch for "ODBC insert
operation has failed for linked table xxx.", followed by my custom error
message embedded in a load of other stuff:
"ODBC-invoegbewerking is mislukt voor gekoppelde tabel xxx.
"[Microsoft][SQL Native Client][SQL Server]My custom error
message.(#50000)[Microsoft][SQL Native Client][SQL Server]The
transaction ended in the trigger. The batch has been aborted.(#3609"

And if the error comes from executing a stored procedure through
QDF.Execute, I get nothing but an error number and the statement that
the ODBC execution failed:
"Fout 3146 tijdens uitvoering:
"ODBC: de oproep is mislukt."

What I would like is to catch either only the third, or the second and
third category of errors. Instead of Access' normal error message, I
want to open a form that shows the most recent row in the logging table.
For errors in the third category, that will always be the custom error
message generated by the trigger or stored procedure. For errors in the
second category - well, I have already indicated that I'm able to work
around them.

I have already tried a crude experiment, using the Form_Error event:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
DoCmd.Beep
DoCmp.OpenFoorm ("Custom_error_form")
Response = acDataErrContinue
End Sub
This works for errors of the third category - but it also opens the
custom error form if I enter "two" in a numeric field, showing a
completely unrelated custom error that happens to be the last error.

What I would like to know is how I can distinguish errors. If the error
is in the third category (forced by RAISERROR from SQL Server), show the
custom error form. For the first category (standard Access error), show
standard error message. And for second category (errors from SQL Server
but not issued by RAISERROR), show standard Access error if possible,
but as I said, I can work around those.

Thanks in advance!!

 

[Matthias Klaey]

Hi,

Sorry for the long message. If you don't want all the details, then just
read the next paragraph and forget about the rest. That's just an
explanation of what I'm trying and why - I welcome any ideas for
improvement, but I really hope to find an answer to this question:

Q: In an Access database (Access 2005 - but answers that apply to all
version from Access 97 are preferred) with linked tables to SQL Server
plus some QueryDef.Execute against same backend, I need to catch only
specific errors. All "standard Access" errors should show the normal
error response as if there's no error handling. All errors induced by
invoking RAISERROR in a SQL Server stored procedure or trigger should
result in the opening of a form. And errors induced by SQL Server but
not through RAISERROR (e.g. constraint violations) should preferably
show the normal response, but I can work aroound it if they also result
in opening the form instead.

Background: I'm working on an Access DB to use as front-end for a SQL
Server DB. I'm using linked tables over ODBC / SQL Native client to
connect Access to SQL Server, and VBA code (QueryDef.SQL = "EXEC MyProc"
/ QueryDef.Execute) to execute stored procedures. And now, I have a
special error handling requirement that I can't figure out how to
implement.

Basically, there are three categories of errors that are relevant:
1) Errors intercepted by Access, e.g. alphabetic info in a numeric field
or entering a value that is not in the list for a listbox.
2) Errors detected by standard constraints in SQL Server, e.g. a NULL
value in a NOT NULL column or a violation of a FOREIGN KEY constraint.
3) Errors detected by custom code, either in the stored procedure or in
a trigger. These are raised by invoking RAISERROR in the SQL Server
code.

Without error handling, the first category shows a clear message,
describing exactly what's wrong. In Dutch, since I'm using a Dutch
version of Access. Great.

The second category shows a mixed-language message. First the Dutch
equivalent of "ODBC: the call has failed", and then (in English, since
SQL Server has no Dutch version) the standard text for the constraint
violation, embedded in lots of extraneous information. For instance:
"ODBC: de oproep is mislukt.
"[Microsoft][SQL Native Client][SQL Server]Cannot insert the value NULL
into column 'xxx', table 'yyy'; column does not allow nulls. INSERT
fails.(#515)[Microsoft][SQL Native Client][SQL Server]The statement has
been terminated.(#3621)"
This is not ideal, but it is acceptable. Since the number of constraints
is limited, I could also add VBA code to check all constraints and show
custom error messages before sending modifications to SQL Server.

But it's the third category that's causing me grief. If a trigger or
stored procedure catches an error, the modification is disallowed, a row
is entered into a logging table with timestamp, userid, and reason for
the error, and RAISERROR is used to display the error message and raise
an error condition. How Access shows this depends on the source of the
problem. If data modification in a linked table causes a trigger to
throw an error, the output I see consists of Dutch for "ODBC insert
operation has failed for linked table xxx.", followed by my custom error
message embedded in a load of other stuff:
"ODBC-invoegbewerking is mislukt voor gekoppelde tabel xxx.
"[Microsoft][SQL Native Client][SQL Server]My custom error
message.(#50000)[Microsoft][SQL Native Client][SQL Server]The
transaction ended in the trigger. The batch has been aborted.(#3609"

And if the error comes from executing a stored procedure through
QDF.Execute, I get nothing but an error number and the statement that
the ODBC execution failed:
"Fout 3146 tijdens uitvoering:
"ODBC: de oproep is mislukt."

What I would like is to catch either only the third, or the second and
third category of errors. Instead of Access' normal error message, I
want to open a form that shows the most recent row in the logging table.
For errors in the third category, that will always be the custom error
message generated by the trigger or stored procedure. For errors in the
second category - well, I have already indicated that I'm able to work
around them.

I have already tried a crude experiment, using the Form_Error event:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
DoCmd.Beep
DoCmp.OpenFoorm ("Custom_error_form")
Response = acDataErrContinue
End Sub
This works for errors of the third category - but it also opens the
custom error form if I enter "two" in a numeric field, showing a
completely unrelated custom error that happens to be the last error.

What I would like to know is how I can distinguish errors. If the error
is in the third category (forced by RAISERROR from SQL Server), show the
custom error form. For the first category (standard Access error), show
standard error message. And for second category (errors from SQL Server
but not issued by RAISERROR), show standard Access error if possible,
but as I said, I can work around those.

Thanks in advance!!

You mean Access 2003? There is no Access 2005

You schould be able to tell the difference between "pure" Access (to
be specific, the VBA) errors and the other two categories by using the
Errors collection:

VBA errors will only fill the Err object, the Errors collection
remains empty.

Also you should handle the erroros in the procedure where they can
occur, not in the Form_Error event. Use the Form_Error event only for
errors like entering the letters "two" in a numerical field.

Below ist some code fragment how I handle the situation in a fully
global error handling function.

It is called like this:

Public Sub TestIt()

On Error GoTo kErrLabel

' errors may arise here...

Exit Sub
kErrLabel: Call kErr("TestIt")
End Sub


Public Sub kErr(strProc As String)

Dim avarErrSave As Variant
Dim lngErrorNr As Long
Dim lngErrCount As Long

' Save full errors collection to Array avarErrSave

' Synchronize Errors collection and Err if neccessary
lngErrCount = Errors.Count - 1
If lngErrCount >= 0 Then
If Errors(lngErrCount).Number <> Err.Number Then
Errors.Refresh
lngErrCount = Errors.Count - 1
End If
End If

' The Errors Collection is not filled for VBA Errors, only for DAO
Errors (and others)
lngErrCount = Errors.Count - 1
If lngErrCount < 0 Then ' VBA Error, Errors Collection is empty, get
all Info from Err Object
lngErrCount = 0
ReDim avarErrSave(0)
avarErrSave(0) = Array(Err.Number, Err.Description, Err.Source,
Err.LastDllError, Erl)
Else ' DAO Error, walk trough Errors Collection. The Err Objects in
there have only Number, Description, Source
ReDim avarErrSave(lngErrCount)
For i = 0 To lngErrCount
avarErrSave(i) = Array(Errors(i).Number, Errors(i).Description,
Errors(i).Source, 0, Erl)
Next
avarErrSave(lngErrCount)(meErr.LastDllError) = Err.LastDllError
End If

On Error GoTo kErrErrLabel ' Error is being destroyed here

' Do the error logging etc


Exit Sub
kErErrLabel: ' Handle errors in error handler
End Sub

HTH
Matthias Kläy

 

[Van T. Dinh]

Hi Hugo

I think you need to trap for ODBC errors differently since the normal Access
VBA error-trapping doesn't get all the errors from ODBC.

See if the following MSKB article helps:

http://support.microsft.com/kb/209855

--
HTH
Van T. Dinh
MVP (Access)

Hi,

Sorry for the long message. If you don't want all the details, then just
read the next paragraph and forget about the rest. That's just an
explanation of what I'm trying and why - I welcome any ideas for
improvement, but I really hope to find an answer to this question:

Q: In an Access database (Access 2005 - but answers that apply to all
version from Access 97 are preferred) with linked tables to SQL Server
plus some QueryDef.Execute against same backend, I need to catch only
specific errors. All "standard Access" errors should show the normal
error response as if there's no error handling. All errors induced by
invoking RAISERROR in a SQL Server stored procedure or trigger should
result in the opening of a form. And errors induced by SQL Server but
not through RAISERROR (e.g. constraint violations) should preferably
show the normal response, but I can work aroound it if they also result
in opening the form instead.

Background: I'm working on an Access DB to use as front-end for a SQL
Server DB. I'm using linked tables over ODBC / SQL Native client to
connect Access to SQL Server, and VBA code (QueryDef.SQL = "EXEC MyProc"
/ QueryDef.Execute) to execute stored procedures. And now, I have a
special error handling requirement that I can't figure out how to
implement.

Basically, there are three categories of errors that are relevant:
1) Errors intercepted by Access, e.g. alphabetic info in a numeric field
or entering a value that is not in the list for a listbox.
2) Errors detected by standard constraints in SQL Server, e.g. a NULL
value in a NOT NULL column or a violation of a FOREIGN KEY constraint.
3) Errors detected by custom code, either in the stored procedure or in
a trigger. These are raised by invoking RAISERROR in the SQL Server
code.

Without error handling, the first category shows a clear message,
describing exactly what's wrong. In Dutch, since I'm using a Dutch
version of Access. Great.

The second category shows a mixed-language message. First the Dutch
equivalent of "ODBC: the call has failed", and then (in English, since
SQL Server has no Dutch version) the standard text for the constraint
violation, embedded in lots of extraneous information. For instance:
"ODBC: de oproep is mislukt.
"[Microsoft][SQL Native Client][SQL Server]Cannot insert the value NULL
into column 'xxx', table 'yyy'; column does not allow nulls. INSERT
fails.(#515)[Microsoft][SQL Native Client][SQL Server]The statement has
been terminated.(#3621)"
This is not ideal, but it is acceptable. Since the number of constraints
is limited, I could also add VBA code to check all constraints and show
custom error messages before sending modifications to SQL Server.

But it's the third category that's causing me grief. If a trigger or
stored procedure catches an error, the modification is disallowed, a row
is entered into a logging table with timestamp, userid, and reason for
the error, and RAISERROR is used to display the error message and raise
an error condition. How Access shows this depends on the source of the
problem. If data modification in a linked table causes a trigger to
throw an error, the output I see consists of Dutch for "ODBC insert
operation has failed for linked table xxx.", followed by my custom error
message embedded in a load of other stuff:
"ODBC-invoegbewerking is mislukt voor gekoppelde tabel xxx.
"[Microsoft][SQL Native Client][SQL Server]My custom error
message.(#50000)[Microsoft][SQL Native Client][SQL Server]The
transaction ended in the trigger. The batch has been aborted.(#3609"

And if the error comes from executing a stored procedure through
QDF.Execute, I get nothing but an error number and the statement that
the ODBC execution failed:
"Fout 3146 tijdens uitvoering:
"ODBC: de oproep is mislukt."

What I would like is to catch either only the third, or the second and
third category of errors. Instead of Access' normal error message, I
want to open a form that shows the most recent row in the logging table.
For errors in the third category, that will always be the custom error
message generated by the trigger or stored procedure. For errors in the
second category - well, I have already indicated that I'm able to work
around them.

I have already tried a crude experiment, using the Form_Error event:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
DoCmd.Beep
DoCmp.OpenFoorm ("Custom_error_form")
Response = acDataErrContinue
End Sub
This works for errors of the third category - but it also opens the
custom error form if I enter "two" in a numeric field, showing a
completely unrelated custom error that happens to be the last error.

What I would like to know is how I can distinguish errors. If the error
is in the third category (forced by RAISERROR from SQL Server), show the
custom error form. For the first category (standard Access error), show
standard error message. And for second category (errors from SQL Server
but not issued by RAISERROR), show standard Access error if possible,
but as I said, I can work around those.

Thanks in advance!!

 

[Hugo Kornelis]

On Fri, 20 Oct 2006 15:49:27 +0200, Matthias Klaey wrote:

(snip)

You mean Access 2003? There is no Access 2005

Hi Matthias,

First of all: thanks for the help so far. Same goes for Van, of course!

And yes, I did mean Access 2003 - that's what you get for concentrating
on SQL Server only. <g>

(snip)

Also you should handle the erroros in the procedure where they can
occur, not in the Form_Error event. Use the Form_Error event only for
errors like entering the letters "two" in a numerical field.

Problem is, many forms require no code at all. The user can enter data,
and Access will save it to the database when the user tabs to the next
record. I could probably fix something up using the before and after
update events, but that would also require me figuring out how to
override the default action Access takes when leaving a record that has
been changed <shudder>. If possible, I'd prefer handling this in the
Form_Event.

For forms containing buttons to call stored procedures, this is indeed
the solution I have now implemented.

You schould be able to tell the difference between "pure" Access (to
be specific, the VBA) errors and the other two categories by using the
Errors collection:

VBA errors will only fill the Err object, the Errors collection
remains empty. (...)
Below ist some code fragment how I handle the situation in a fully
global error handling function.

Both your solution and the MSKB article Van links to rely on
Errors.Count. I've done some experiments, but somehow couldn't get it to
run. Here is what I have now, to test if all works as expected:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Dim errX As DAO.Error, lErrCount As Long
lErrCount = Errors.Count - 1
' Synchronize Errors collection and Err if neccessary
If lErrCount >= 0 Then
If Errors(lngErrCount).Number <> Err.Number Then
Errors.Refresh
lngErrCount = Errors.Count - 1
End If
End If

MsgBox (CStr(Errors.Count))
If Errors.Count > 1 Then
MsgBox ("ODBC error")
For Each errX In DAO.Errors
Debug.Print "ODBC Error"
Debug.Print errX.Number
Debug.Print errX.Description
Next errX
Else
MsgBox ("VBA error")
Debug.Print "VBA Error"
Debug.Print Err.Number
Debug.Print Err.Description
End If
End Sub

As you see, I combined your code to refresh the errors collection (why
is that needed, BTW?) with the basic outline of the code in the KB
article and added some message boxes to see what happpens.

When entering "ab" in a numeric field, I first see a message box with
the text "0", then a box wih the text "VBA Error", then the Dutch
equivalent of "Invalid value for this field". Great.
When entering data that looks valid to Access but will be rejected by
the trigger, I first see a message box with the text "0", then a box wih
the text "VBA Error", then the Dutch equivalent of "ODBC insert
operation failed for linked table XXX", followed by the error message
from the trigger (in between various [Microsoft] and other stuff).

Why doesn't this work as expected?

 

[Matthias Klaey]

Hi Hugo

]

Also you should handle the erroros in the procedure where they can
occur, not in the Form_Error event. Use the Form_Error event only for
errors like entering the letters "two" in a numerical field.

Problem is, many forms require no code at all. The user can enter data,
and Access will save it to the database when the user tabs to the next
record. I could probably fix something up using the before and after
update events, but that would also require me figuring out how to
override the default action Access takes when leaving a record that has
been changed <shudder>. If possible, I'd prefer handling this in the
Form_Event.

For forms containing buttons to call stored procedures, this is indeed
the solution I have now implemented.
[...]
As you see, I combined your code to refresh the errors collection (why
is that needed, BTW?) with the basic outline of the code in the KB
article and added some message boxes to see what happpens.

When entering "ab" in a numeric field, I first see a message box with
the text "0", then a box wih the text "VBA Error", then the Dutch
equivalent of "Invalid value for this field". Great.
When entering data that looks valid to Access but will be rejected by
the trigger, I first see a message box with the text "0", then a box wih
the text "VBA Error", then the Dutch equivalent of "ODBC insert
operation failed for linked table XXX", followed by the error message
from the trigger (in between various [Microsoft] and other stuff).

Why doesn't this work as expected?

First, the Form_Error event really behaves very differently in that
neither the Err object nor the Errors collection is filled. You only
get the DataErr value to work with, and I see no immedidate workaround
for this. You can try to use the AccessError function to differentiate
between the error types. Perhaps you can get away with something like
the following:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If InStr(AccessError(DataErr), "ODBC") > 0 Then
Response = acDataErrContinue
' open your own error form here
Else
Response = acDataErrDisplay
End If
End Sub


Second, in my error handling code for VBA, there still is a problem.
This is due to the fact that once the Errors collection is filled, it
remains filled. Even a code reset does not empty it. You have to shut
down Access completely in order to get it cleared.

I use the Errors.Refresh call because i have sporadically seen that
after a DAO/ODBC error the Err object is not properly synchronized
with the topmost error in the Errors collection. I am not able to
recreate this situation by will, but the Refresh will not hurt in any
case.

So my general error handling code now looks like this:

Dim i As Integer
Dim intErrorsCount As Integer
Dim lIsVBAError As Boolean

Errors.Refresh ' If error comes from DAO, Err object and Errors
' collection should now be synchronized
intErrorsCount = Errors.Count
lIsVBAError = False
If intErrorsCount = 0 Then
' No DAO error has yet occurred
lIsVBAError = True
Else
' If the numbers in the Err object and in the topmost error of the
' Errors collection do not match, it is a VBA error, and the
' Errors collection contains old information from a
' previous DAO error.
lIsVBAError = Err.Number <> Errors(intErrorsCount - 1).Number
End If

If lIsVBAError Then
Debug.Print "VBA Error"
Debug.Print "Err Object: " & Err.Number & "; " & _
Err.Description & " Source: " & Err.Source
Else
Debug.Print "DAO/ODBC/SQL Error"
For i = Errors.Count - 1 To 0 Step -1
Debug.Print i & ": " & Errors(i).Number & "; " & _
Errors(i).Description & " Source: " & _
Errors(i).Source
Next
End If

With kind regards
Matthias Kläy

 

[Hugo Kornelis]

On Tue, 24 Oct 2006 01:54:00 +0200, Matthias Klaey wrote:

(snip)

First, the Form_Error event really behaves very differently in that
neither the Err object nor the Errors collection is filled. You only
get the DataErr value to work with, and I see no immedidate workaround
for this. You can try to use the AccessError function to differentiate
between the error types. Perhaps you can get away with something like
the following:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If InStr(AccessError(DataErr), "ODBC") > 0 Then
Response = acDataErrContinue
' open your own error form here
Else
Response = acDataErrDisplay
End If
End Sub

Hi Matthias,

While this is probably exactly as much of an ugly kludge as it looks to
the uninitiated me, it does manage to do the job.

Thanks! (Or should I say, "Danke schön"?)

 

[Matthias Klaey]

On Tue, 24 Oct 2006 01:54:00 +0200, Matthias Klaey wrote:

(snip)

Hi Matthias,

While this is probably exactly as much of an ugly kludge as it looks to
the uninitiated me, it does manage to do the job.

Thanks! (Or should I say, "Danke schön"?)

Hi Hugo

Bitte sehr (I'm sorry I don't know the Dutch version -
I'm glad that this works for you. Yes it is pretty much a kludge based
on the assumption that DataErr will point to some vacuous message like
"ODBC call failed". There is of course no guarantee that this will
work under all circumstances.
Why there is no access to the full error information in this situation
is beyond me - you must ask the Gods of Access.

Greetings
Matthias Kläy