不能捕获 OnError 属性的窗体 (KB 206175) 中的特定 ODBC 错误

注意: 我们希望能够尽快以你的语言为你提供最新的帮助内容。本页面是自动翻译的,可能包含语法错误或不准确之处。我们的目的是使此内容能对你有所帮助。可以在本页面底部告诉我们此信息是否对你有帮助吗?请在此处查看本文的英文版本以便参考。

如果 Access 窗体的 OnError 属性设置为事件过程中时,无法检索该过程中,在 ODBC 错误的说明,也不能捕获特定 ODBC 错误。传递给错误事件过程的唯一信息 ODBC 错误时,是常规错误,如 3146,它对应于错误消息的数: ODBC 呼叫失败。

原因

ODBC 错误消息通常由两个组件组成。第一个组件是的错误 3146 的说明:

ODBC 呼叫失败

在第二个组件,可用于检索错误号如说明中包含服务器特定错误的信息:

[Microsoft][ODBC SQL Server 驱动程序][SQL Server] < 服务器特定错误消息 > (#< 错误号 >)

如果窗体的OnError属性设置为事件过程中时,您可以捕获的错误,第一个组件数,但无法捕获的第二个组件数。服务器特定信息的 ODBC 错误的第二部分中,将出现在屏幕上代码完成之后运行,除非在事件包括以下行过程:

响应 = acDataErrContinue

分辨率

注意: Microsoft 提供的编程示例仅用于演示,没有明示或隐含。这包括但不限于适销或特殊用途的适用性的默示担保。本文假定您熟悉所演示的编程语言和用于创建和调试过程的工具。Microsoft 支持工程师可以帮助解释某个特定过程的功能,但它们不会修改这些示例,以提供额外的功能或构建过程以满足您的特定要求。

您可以创建 Microsoft Visual Basic for 使用数据访问对象 (DAO) 更新基于该窗体RecordsetClone的应用程序过程。这允许您捕获您会收到任何错误消息。

DAO 包含可用于捕获 ODBC 错误的第二部分中的服务器特定信息错误集合。ODBC 错误时,第一个组件存储在错误集合中,第一个元素和第二个组件存储在第二个元素。

本文中的示例使用该事件,而不是错误事件捕获特定 ODBC 错误。若要创建窗体的更新前事件发生时捕获特定 ODBC 错误的函数,请按照下列步骤:

  1. 创建空白桌面数据库。

  2. 链接到 Microsoft SQL Server 中 AdventureWorks 示例数据库中的 dbo_Accounts 表。

  3. 使用窗体向导-纵栏布局来创建新表单基于客户表。

  4. 将表单保存为 frmAccounts。

  5. 创建新的模块,并且如果该行已不存在,然后在声明部分中键入以下行:

    显式选项

  6. 键入或粘贴到模块以下过程:

    Public Function SaveRecODBC(SRO_form As Form) As Boolean
    ' ***************************************************************
    ' Function: SaveRecODBC
    ' Purpose: Updates a form based on a linked ODBC table
    ' and traps any ODBC errors.
    ' Arguments: SRO_Form, which refers to the form.
    ' Returns: True if successful or False if an error occurs.
    ' ***************************************************************
    On Error GoTo SaveRecODBCErr
    Dim fld As Field, ctl As Control
    Dim errStored As Error
    Dim rc As DAO.Recordset
    
    ' Check to see if the record has changed.
    If SRO_form.Dirty Then
        Set rc = SRO_form.Recordset.Clone
        If SRO_form.NewRecord Then
            rc.AddNew
            For Each ctl In SRO_form.Controls
                ' Check to see if it is the type of control
                ' that has a ControlSource.
                If ctl.ControlType = acTextBox Or _
                    ctl.ControlType = acComboBox Or _
                    ctl.ControlType = acListBox Or _
                    ctl.ControlType = acCheckBox Then
                    ' Verify that a value exists in the ControlSource.
                    If ctl.Properties("ControlSource") <> "" Then
                        ' Loop through the fields collection in the
                        ' RecordsetClone. If you find a field name
                        ' that matches the ControlSource, update the
                        ' field. If not, skip the field. This is
                        ' necessary to account for calculated controls.
                        For Each fld In rc.Fields
                            ' Find the field and verify
                            ' that it is not Null.
                            ' If it is Null, don't add it.
                            If fld.Name = ctl.Properties("ControlSource") _
                            And Not IsNull(ctl) Then
                                fld.Value = ctl
                                ' Exit the For loop
                                ' if you have a match.
                                Exit For
                            End If
                        Next fld
                    End If ' End If ctl.Properties("ControlSource")
                End If ' End If ctl.controltype
            Next ctl
            rc.Update
        Else
            ' This is not a new record.
            ' Set the bookmark to synchronize the record in the
            ' RecordsetClone with the record in the form.
            rc.Bookmark = SRO_form.Bookmark
            rc.Edit
            For Each ctl In SRO_form.Controls
                ' Check to see if it is the type of control
                ' that has a ControlSource.
                If ctl.ControlType = acTextBox Or _
                    ctl.ControlType = acComboBox Or _
                    ctl.ControlType = acListBox Or _
                    ctl.ControlType = acCheckBox Then
                    ' Verify that a value exists in the
                    ' ControlSource.
                    If ctl.Properties("ControlSource") <> "" Then
                        ' Loop through the fields collection in the
                        ' RecordsetClone. If you find a field name
                        ' that matches the ControlSource, update the
                        ' field. If not, skip the field. This is
                        ' necessary to account for calcualted controls.
                        For Each fld In rc.Fields
                            ' Find the field and make sure that the
                            ' value has changed. If it has not
                            ' changed, do not perform the update.
                            If fld.Name = ctl.Properties("ControlSource") _
                                And fld.Value <> ctl And _
                                Not IsNull(fld.Value <> ctl) Then
                                fld.Value = ctl
                                ' Exit the For loop if you have a match.
                                Exit For
                            End If
                        Next fld
                    End If ' End If ctl.Properties("ControlSource")
                End If ' End If ctl.controltype
            Next ctl
            rc.Update
        End If ' End If SRO_form.NewRecord
    End If ' End If SRO_form.Dirty
    ' If function has executed successfully to this point then
    ' set its value to True and exit.
    SaveRecODBC = True
    
    Exit_SaveRecODBCErr:
        Exit Function
    
    SaveRecODBCErr:
    ' The function failed because of an ODBC error.
    ' Below are a list of some of the known error numbers.
    ' If you are not receiving an error in this list,
    ' add that error to the Select Case statement.
    For Each errStored In DBEngine.Errors
        Select Case errStored.Number
            Case 3146 ' No action -- standard ODBC--Call failed error.
            Case 2627 ' Error caused by duplicate value in primary key.
                MsgBox "You tried to enter a duplicate value in the Primary Key."
            Case 3621 ' No action -- standard ODBC command aborted error.
            Case 547 ' Foreign key constraint error.
                MsgBox "You violated a foreign key constraint."
            Case Else ' An error not accounted for in the Select Case ' statement.
                On Error GoTo 0
                Resume
        End Select
    Next errStored
    SaveRecODBC = False
    Resume Exit_SaveRecODBCErr
    
    End Function
    
  7. 使用唯一名称保存该模块并关闭模块窗口。

  8. 将 frmAccounts 窗体的更新前属性设置为以下事件过程:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    ' If you can save the changes to the record undo the changes on the form.
    If SaveRecODBC(Me) Then Me.Undo
    ' If this is a new record go to the last record on the form.
    If Me.NewRecord Then
        RunCommand acCmdRecordsGoToLast
    Else
        ' If you can't update the record, cancel the BeforeUpdate event.
        Cancel = -1
    End If
    End Sub
    
  9. 调试菜单上,单击编译 < 数据库名称 >

  10. 如果没有错误,请保存窗体。

  11. 打开 frmAccounts 窗体,然后添加一条新记录或编辑记录。

    当您对记录进行更改时,移到另一条记录时保存记录。如果出现 ODBC 错误,请参阅基于服务器特定错误,自定义消息和常规"ODBC-呼叫失败"捕获邮件。

扩展你的 Office 技能
了解培训
抢先获得新功能
加入 Office 预览体验计划

此信息是否有帮助?

谢谢您的反馈!

谢谢你的反馈! 可能需要转接到 Office 支持专员。

×