Access 2007 ODBC Error Trapping

[AG]

Windows XP Pro SP2
Access 2007 (12.0.6334.5000) SP1
Jet 12.0.6211.1000
Using ODBC linked tables to SQL Server 2005 9.0.3077 SP2

Form bound to linked ODBC table.
How can I get the correct error info when Access attempts to save a record
and an error occurs?

I can trap it in the form error event, but can't seem to find a way to
collect the correct info.
Access reports the usual 3146 ODBC call failed.
From what I have found in searching the web, I should be able to get it from
the dbEngine.Errors collection.
dbEngine.Errors seems to work when performing operations in DAO code, but
not with bound forms.
When I trap the error in the form error event and check the dbEngine.Errors
collection,
it always reports 3021 (No current record), no matter what the actual error
happens to be.

Can anyone offer a solution.

 

[Mark Han[MSFT]]

Hi AG,

Thank you for using Microsoft MSDN Managed Newsgroup. My name is Mark Han.
I am glad to work with you on this issue.

From your description, I understand that you would like to trap the error
in the form error event. However the error captured is always 3021. not the
actual error code. If I have misunderstood, please let me know.

in order to address your concern, I make an example here.

Visual Basic Error event arises when a run-time error occurs in Access on
the current form.

Error Event Visual Basic Example:
It is good practice to have an On Error event handler in each form. In
this example we simply trap the error and display a message to the userr.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Msgbox "An unexpected error has occurred in the form. " & _
"The error description is " & err.description
Response = acDataErrContinue
End Sub

The response argument can have either of two values:

acDataErrContinue: Ignore the error and continue without displaying the
Microsoft Access error message.


acDataErrDisplay: Display the Microsoft Access error message. This is the
default setting.

Hope the above helpful.

Besides, in order to better assist you with the issue, if it is convenient
to you, please post the error event code here.

I look forward to hearing from you.

Best regards,
Mark Han
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or
a Microsoft Support Engineer within 2 business day is acceptable. Please
note that each follow up response may take approximately
2 business days as the support professional working with you may need
further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are
best handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx

============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[AG]

Mark,

Thanks for replying, but obviously, you did not read and understand my
problem.
All you posted was a canned reply.
I specified that I am working with ODBC and the dbengine errors collection,
which is reporting the wrong error.
Plus, you code is completely wrong.
In the Form_Error event, the error number is contained in the DataErr
argument, NOT the access error object.
In the Form_Error event, Err.Number is 0 and Err.Description is empty.

As I already specified, the error is 3146, ODBC call failed.
At that point, I should be able to get the real error from the dbengine
errors collection, but that is reporting the incorrect error.

No, your response is not at all helpful.

Sample code?

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Dim strErr As String
Dim intErr As Integer

strErr = "Access error " & CStr(DataErr) & " " & AccessError(DataErr)
If DBEngine.Errors.Count > 0 Then
strErr = strErr & vbCrLf & "DbEngineErrors:"
For intErr = 0 To DBEngine.Errors.Count - 1
strErr = strErr & vbCrLf & DBEngine.Errors(intErr).Number & " / " &
DBEngine.Errors(intErr).Description & " / " & DBEngine.Errors(intErr).Source
Next
End If
MsgBox strErr, vbCritical, "Not What Is Desired"
End Sub

This result is:
Access error 3146 ODBC--call failed.
DbEngineErrors:
3021 / No current record. / DAO.Recordset

I would expect something like:

Access error 3146 ODBC--call failed.
DbEngineErrors:
515 / ...column does not allow null... / DAO.Recordset

Or, am I looking in the wrong place to get the actual error?

Can anyone offer a solution?

--

AG
Email: npATadhdataDOTcom

 

[Mark Han[MSFT]]

Hi AG,

Thank you for the update.

I misunderstood your concern yesterday. Sorry for the inconvenience.
Regarding to your description, I better understand the issue.

your concern is that the error captured by access and dbEngine.Errors
collection is different; and it seems that the dbengine
errors collection reports incorrected error. if I misunderstand anything,
please tell me directly.

in order to address your concern, I would like to explain the following
1 An Errors collection contains all stored Error objects, each of which
pertains to a single operation involving DAO. Any operation involving DAO
objects can generate one or more errors. As each error occurs, one or more
Error objects are placed in the Errors collection of the DBEngine object.

Based on the above, we know that DBEngine.Errors only captures the detail
error message on the operation involving DAO.

there is an article to share with
you:http://msdn.microsoft.com/en-us/library/bb177461.aspx

2 How To Get More Information on the ODBC Call Failed Error: there is an
article to share with you:http://support.microsoft.com/kb/161288

I know you are our senrio customer. it is my pleasure to assist you with
the issue.

Have a nice day.

Best regards,
Mark Han
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[AG]

Mark,

You obviously still don't understand the problem.
I have already told you that the errrors collection contains NO errors, as I
am dealing with a bound form.
Why then, do you point me to an article that is completely not applicable?

Please pass this issue on to someone who might possibly understand it.

 

[Charles Wang [MSFT]]

Hi AG,
I have sent an email to you regarding this issue. Please check it.

To share it with the community, I extract part of the content and post it
here:
=============================================================
The question you asked is actually a by design limitation in Access, no
matter in 2007 or its earlier version.

An ODBC error message is composed with two parts:
<Part 1>ODBC call failed.
<Part 2> [Microsoft][ODBC SQL Server Driver][SQL Server] <Server-specific
error message>

By design the first part can be trapped by the code specified in the
OnError property for a form, however the second part cannot. As you might
have seen, no matter what you had written in the OnError procedure, the
second part of error message box always appeared on the screen after the
event procedure code finished running. There is an old KB article
dedicatedly addressing this issue:
ACC: Cannot Trap ODBC Errors with Form OnError Property Code
http://support.microsoft.com/kb/124395/en-us

If we look at the RESOLUTION part of the document, we can see the following
description:
"If the ODBC error is triggered by a procedure that contains a routine to
handle errors (rather than by a form), the procedure will be able to trap
both parts of the error message."

In other words, if you want to trap the second part of the error message,
you need to avoid using bound form, instead write your custom code to query
and update data in a procedure and trap the error message in the procedure.
I write the following test code for your reference:
------------------------------------------------
Private Sub Form_Load()
On Error GoTo Error_Trap
Dim strErr As String
Dim intErr As Integer
Dim mydb As Database
Dim myq As QueryDef
Set mydb = CurrentDb()
Set myq = mydb.CreateQueryDef("")
myq.Connect =
"ODBC;DSN=my2k5;UID=;PWD=;LANGUAGE=us_english;DATABASE=Northwind"

myq.ReturnsRecords = False

' Any SQL statement will work below.
myq.SQL = "update dbo.customers set companyname='Ernst Handel' where
customerid='EASTC' "
myq.Execute
Exit Sub

Error_Trap:


'strErr = "Access error " & CStr(DataErr) & " " & AccessError(DataErr)
If DBEngine.Errors.Count > 0 Then
strErr = strErr & vbCrLf & "DbEngineErrors:"
For intErr = 0 To DBEngine.Errors.Count - 1
strErr = strErr & vbCrLf & DBEngine.Errors(intErr).Number & " / "
& DBEngine.Errors(intErr).Description & " / " &
DBEngine.Errors(intErr).Source
Next
End If
MsgBox strErr, vbCritical, "Not What Is Desired"

End Sub
----------------------------------------------------------------------

Not sure why the KB article is not updated to apply to Access 2003 and 2007
yet. I have submitted a feedback to our document team for this.
=====================================================

Any further questions or concerns are welcomed!

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[AG]

Charles,

Thanks for the reply.

Yes, you got it all correct; and only one try .
I was afraid that there would be no way to access the message.
So, I will just have to live with it.

A few comments/questions though.

1. As you mentioned, the article applies to Access 2.0 and 95 and should be
updated, or another issued to cover newer versions of Access.

2. Unbound forms don't apply to continuous forms or datasheets, so in this
case I will just have to live with it. I do perform data validation before
the record is saved, so hopefully, the user will never see an error like
that, but the unexpected does happen.

3. This is obviously a very long-standing issue. Why has it not been
corrected?
Obviously, the Access application does have access to the error. It should
not be to difficult to make it available to vba code. Even just the text of
the message, without breaking it down into error codes, would be very
helpful.

--

AG
Email: npATadhdataDOTcom

Hi AG,
I have sent an email to you regarding this issue. Please check it.

To share it with the community, I extract part of the content and post it
here:
=============================================================
The question you asked is actually a by design limitation in Access, no
matter in 2007 or its earlier version.

An ODBC error message is composed with two parts:
<Part 1>ODBC call failed.
<Part 2> [Microsoft][ODBC SQL Server Driver][SQL Server] <Server-specific
error message>

By design the first part can be trapped by the code specified in the
OnError property for a form, however the second part cannot. As you might
have seen, no matter what you had written in the OnError procedure, the
second part of error message box always appeared on the screen after the
event procedure code finished running. There is an old KB article
dedicatedly addressing this issue:
ACC: Cannot Trap ODBC Errors with Form OnError Property Code
http://support.microsoft.com/kb/124395/en-us

If we look at the RESOLUTION part of the document, we can see the
following
description:
"If the ODBC error is triggered by a procedure that contains a routine to
handle errors (rather than by a form), the procedure will be able to trap
both parts of the error message."

In other words, if you want to trap the second part of the error message,
you need to avoid using bound form, instead write your custom code to
query
and update data in a procedure and trap the error message in the
procedure.
I write the following test code for your reference:
------------------------------------------------
Private Sub Form_Load()
On Error GoTo Error_Trap
Dim strErr As String
Dim intErr As Integer
Dim mydb As Database
Dim myq As QueryDef
Set mydb = CurrentDb()
Set myq = mydb.CreateQueryDef("")
myq.Connect =
"ODBC;DSN=my2k5;UID=;PWD=;LANGUAGE=us_english;DATABASE=Northwind"

myq.ReturnsRecords = False

' Any SQL statement will work below.
myq.SQL = "update dbo.customers set companyname='Ernst Handel' where
customerid='EASTC' "
myq.Execute
Exit Sub

Error_Trap:


'strErr = "Access error " & CStr(DataErr) & " " & AccessError(DataErr)
If DBEngine.Errors.Count > 0 Then
strErr = strErr & vbCrLf & "DbEngineErrors:"
For intErr = 0 To DBEngine.Errors.Count - 1
strErr = strErr & vbCrLf & DBEngine.Errors(intErr).Number & " / "
& DBEngine.Errors(intErr).Description & " / " &
DBEngine.Errors(intErr).Source
Next
End If
MsgBox strErr, vbCritical, "Not What Is Desired"

End Sub
----------------------------------------------------------------------

Not sure why the KB article is not updated to apply to Access 2003 and
2007
yet. I have submitted a feedback to our document team for this.
=====================================================

Any further questions or concerns are welcomed!

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.
=========================================================

 

[Charles Wang [MSFT]]

Hi AG,
I think that we can find some explainations from the STATUS section, we can
see the following description:
======================================
Microsoft has confirmed this to be a problem in Microsoft Access 2.0 and
7.0. This behavior has been changed in Microsoft Access 97.

NOTE: Although this behavior has changed in Microsoft Access 97, you can
still not trap the second part of the error. An event does fire when there
is an error; however, there is no retrievable information about the error
message. The event that fires only helps you to hide existing errors from a
user.
=======================================
As you can see, though Access 97 was not added to the "APPLIES TO" section,
it still could not trap the second part of the error. The article was not
updated since November 17, 2000. For the reason, I think that it might be
caused by the fact that more and more applications are being developped
directly based on SQL Server products since then and this issue was not
reported as many as before, so it does not cause much attention from our
product team.

For your last concern, "This is obviously a very long-standing issue. Why
has it not been corrected? Obviously, the Access application does have
access to the error. It should not be to difficult to make it available to
vba code. Even just the text of the message, without breaking it down into
error codes, would be very helpful.", I totally agree with you and though I
internally submit a feedback to our product team, to attract their more
attention, I also recommend that you submit a feedback at the bottom of the
article to let our product and document team know it.

For the resolution, based on your requirements, unfortunately there is
indeed no grace resolution now. Unless this feature has been improved, I
am afraid that you may have to live with it.

Thank you for your understanding!

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[AG]

Thanks Charles,

I have submitted the feedback.