If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Access 2007 ODBC Error Trapping
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. -- AG Email: npATadhdataDOTcom |
#2
|
|||
|
|||
Access 2007 ODBC Error Trapping
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: . ================================================== ========= Get notification to my posts through email? Please refer to http://msdn.microsoft.com/subscripti...ult.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/subs.../aa948874.aspx ================================================== ========== This posting is provided "AS IS" with no warranties, and confers no rights. ================================================== ======= |
#3
|
|||
|
|||
Access 2007 ODBC Error Trapping
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]" wrote in message ... 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: . ================================================== ========= Get notification to my posts through email? Please refer to http://msdn.microsoft.com/subscripti...ult.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/subs.../aa948874.aspx ================================================== ========== This posting is provided "AS IS" with no warranties, and confers no rights. ================================================== ======= |
#4
|
|||
|
|||
Access 2007 ODBC Error Trapping
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: . ================================================== ======= This posting is provided "AS IS" with no warranties, and confers no rights. ================================================== ======= |
#5
|
|||
|
|||
Access 2007 ODBC Error Trapping
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. -- AG Email: npATadhdataDOTcom "Mark Han[MSFT]" wrote in message ... 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: . ================================================== ======= This posting is provided "AS IS" with no warranties, and confers no rights. ================================================== ======= |
#6
|
|||
|
|||
Access 2007 ODBC Error Trapping
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 he ================================================== =========== 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 1ODBC 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;DATA BASE=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: . ================================================== ======= This posting is provided "AS IS" with no warranties, and confers no rights. ================================================== ======= |
#7
|
|||
|
|||
Access 2007 ODBC Error Trapping
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 ""Charles Wang [MSFT]"" wrote in message ... 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 he ================================================== =========== 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 1ODBC 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;DATA BASE=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: . ================================================== ======= This posting is provided "AS IS" with no warranties, and confers no rights. ================================================== ======= |
#8
|
|||
|
|||
Access 2007 ODBC Error Trapping
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: . ================================================== ======= This posting is provided "AS IS" with no warranties, and confers no rights. ================================================== ======= |
#9
|
|||
|
|||
Access 2007 ODBC Error Trapping
Thanks Charles,
I have submitted the feedback. -- AG Email: npATadhdataDOTcom ""Charles Wang [MSFT]"" wrote in message ... 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: . ================================================== ======= This posting is provided "AS IS" with no warranties, and confers no rights. ================================================== ======= |
Thread Tools | |
Display Modes | |
|
|