Microsoft KB Archive/301693

= ACC2000: Form Error Event Does Not Return Native SQL Server Errors in an Access Project =

Article ID: 301693

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q301693



Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access project (.adp).



SYMPTOMS
When you use the form Error event within a Microsoft Access project (.adp) file, the DataErr argument returns error 2757 for all data-related errors encountered on the form. Because the DataErr argument always returns error 2757, it is impossible to programmatically determine what error has occurred on the form.



CAUSE
This behavior occurs because Microsoft Access uses an OLE layer to communicate with SQL Server. The error returned to the form Error event is a generic OLE error. The native SQL Server errors are not passed back through this layer to the form.



RESOLUTION
You can use ActiveX Data Objects (ADO) to programmatically retrieve some error information, although you can only retrieve the error description, and not its actual number. You can parse the error description to determine the kind of error that SQL Server returned, and then take some action based on that. You can do this by using the ADO recordset RecordChangeComplete event with your form's recordset. The following steps demonstrate how to do this with the Customers form in the NorthwindCS sample project.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.  Open the sample project NorthwindCS.adp. Open the Customers form in Design view. On the View menu, click Code to open the module of the form in the Visual Basic Editor.  Add the following code to the module: Option Compare Database Option Explicit

Private WithEvents rs As ADODB.Recordset

Private Sub Form_Open(Cancel As Integer) Set rs = Me.Recordset End Sub

Private Sub Form_Error(DataErr As Integer, Response As Integer) 'Disable the error that Access displays If DataErr = 2757 Then 'SQL Server returned an error, we will ignore the 'the error and handle it with the recordset event sink Response = acDataErrContinue Else 'write your custom code here to handle non 'SQL Server errors End If End Sub

Private Sub rs_RecordChangeComplete(ByVal adReason As ADODB.EventReasonEnum, _                                   ByVal cRecords As Long, ByVal pError As ADODB.Error, _                                    adStatus As ADODB.EventStatusEnum, _                                    ByVal pRecordset As ADODB.Recordset) Dim strError As String Dim strNewError As String Dim intPosition As Integer Dim strFieldName As String 'Check the status of the update to see if   'errors occurred If adStatus = adStatusErrorsOccurred Then strError = pError.Description If InStr(strError, &quot;Cannot insert the value NULL into column&quot;) > 0 Then 'Tried to insert a null into a non-nullable field intPosition = InStr(strError, &quot;'&quot;) strFieldName = Mid(strError, intPosition + 1, _               InStr(intPosition + 1, strError, &quot;'&quot;) - (intPosition + 1)) strNewError = &quot;'&quot; & strFieldName & &quot;' may not be null. &quot; & _ &quot;Please enter a value for '&quot; & strFieldName & &quot;'.&quot; MsgBox strNewError, vbExclamation ElseIf InStr(strError, &quot;Non-nullable column cannot be updated to Null&quot;) > 0 Then strNewError = &quot;This column may not be null.&quot; MsgBox strNewError, vbExclamation ElseIf _ InStr(strError, &quot;DELETE statement conflicted with column reference constraint&quot;) > 0 Then 'Tried to violate referential integrity strNewError = &quot;You cannot delete this record. It contains related records in another table.&quot; MsgBox strNewError, vbExclamation ElseIf _ InStr(strError, &quot;Violation of Primary Key Constraint&quot;) > 0 Or _ InStr(strError, &quot;Violation of Unique Key Constraint&quot;) > 0 Or _ InStr(strError, &quot;Cannot insert duplicate key row&quot;) > 0 Then strNewError = &quot;You entered a duplicate value into a uniquely indexed field.&quot; & _ &quot; Please enter another value.&quot; MsgBox strNewError, vbExclamation ElseIf _ InStr(strError, &quot;UPDATE statement conflicted with column check constraint&quot;) > 0 Then intPosition = InStr(strError, &quot;column '&quot;) + 8 strFieldName = Mid(strError, intPosition, _                   InStrRev(strError, &quot;'&quot;) - intPosition) strNewError = &quot;You violated a check constraint on '&quot; & strFieldName & &quot;'.&quot; MsgBox strNewError, vbExclamation Else MsgBox strError, vbExclamation End If   End If End Sub  On the File menu, click Close and Return to Microsoft Access. Save the form, and then close it.</li> Open the Customers form in Form view.</li> Move to a new record on the form.</li> Enter a value for the CompanyName field. Do not enter a value for the CustomerID field.</li> On the Records menu, click Save Record.</li></ol>

Note that you receive the following custom error message:

'CustomerID' may not be null. Please enter a value for 'CustomerID'.

<div class="status_section">

STATUS
Microsoft has confirmed that this is a problem in Microsoft Access 2000. This problem was corrected in Microsoft Access 2002.

<div class="moreinformation_section">

Steps to Reproduce the Behavior
<ol> CAUTION: If you follow the steps in this example, you modify the sample Access project NorthwindCS.adp. You may want to back up the NorthwindCS.adp file and follow these steps on a copy of the project.

</li> Open the sample project NorthwindCS.adp.</li> Open the Customers form in Design view.</li> On the View menu, click Code to open the module of the form in the Visual Basic Editor.</li>  Add the following code to the module: Private Sub Form_Error(DataErr As Integer, Response As Integer) Dim strError As String strError = AccessError(DataErr) MsgBox strError,, &quot;Error &quot; & DataErr End Sub </li> On the File menu, click Close and Return to Microsoft Access.</li> Save the form, and then close it.</li> Open the form in Form view.</li> Move to a new record on the form.</li> Enter a value for the CompanyName field. Do not enter a value for the CustomerID field.</li> On the Records menu, click Save Record.</li></ol>

Note that you receive the following errors:

Error 2757

There was a problem accessing a property or method of the OLE object. Try one or more of the following:

-Verify that the OLE server is registered correctly by reinstalling it.

-Make sure your computer is connected to the server on which the OLE server application resides.

-Close the OLE server and restart it outside of Microsoft Access. Then try the original operation again from within Microsoft Access.

This is followed by the actual error returned from Microsoft SQL Server:

Cannot insert the value NULL into column 'CustomerID', table 'NorthwindCS.dbo.Customers'; column does not allow nulls.

Additional query words: prb pra

Keywords: kbbug kbnofix kbclientserver KB301693

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.