Ne more pasti določenih napak ODBC v lastnosti OnError obrazca (KB 206175)

Če lastnost OnError Accessovega obrazca nastavite na dogodkovno proceduro, ne morete pridobiti opisa napake ODBC v tem postopku, prav tako pa ne morete pasti določene napake ODBC. Ko pride do napake ODBC, so v dogodkovno proceduro Napake posredovane le informacije o napaki, kot je na primer 3146, ki ustreza sporočilu o napaki: napaka ODBC-Call uspela.

Vzrok

Sporočila o napakah ODBC so običajno sestavljena iz dveh komponent. Prva komponenta je napaka 3146, opis pa je:

KLIC ODBC ni uspel

Informacije o napakah, značilne za strežnik, so v drugi komponenti, iz katere lahko pridobite številko napake in opis, na primer:

[Microsoft][ODBC SQL Server Driver][SQL Server] Sporočilo o <napaki, specifično za strežnik> (#<številka napake>)

Če lastnost OnError obrazca nastavite na dogodkovno proceduro, lahko nastavite število prve komponente napake, ne morete pa pasti števila druge komponente. Informacije, značilne za strežnik, so v drugem delu napake ODBC prikazane na zaslonu, ko se koda konča, razen če v dogodkovno proceduro vključite to vrstico:

Odziv = acDataErrContinue

Rešitev

Ustvarite lahko Postopek microsoft VBA, ki uporablja predmete za dostop do podatkov (DAO) za posodobitev RecordsetClone, ki temelji na obrazcu. Tako lahko ujamete vsa sporočila o napaki, ki jih prejmete.

DAO vsebuje zbirko napak , ki jo lahko uporabite za past informacij v strežniku v drugem delu napake ODBC. Ko pride do napake ODBC, je prva komponenta shranjena v prvem elementu zbirke napak, druga komponenta pa je shranjena v drugem elementu.

V primeru v tem članku je namesto dogodka Error uporabljen dogodek BeforeUpdate za past določenih napak ODBC. Če želite ustvariti funkcijo, ki ujame določene napake ODBC, ko pride do dogodka BeforeUpdate obrazca, sledite tem korakom:

1. Ustvarite prazno namizno zbirko podatkov.

2. Ustvarite povezavo do dbo_Accounts v vzorčni zbirki podatkov AdventureWorks v storitvi Microsoft SQL Server.

3. S čarovnikom za obrazce – postavitev Columnar ustvarite nov obrazec na osnovi tabele računov.

4. Shranite obrazec kot frmAccounts.

5. Ustvarite nov modul in nato v razdelek Deklaracije vnesite to vrstico, če ta vrstica še ni na voljo:
   Option Explicit

6. V modul vnesite ali prilepite ta postopek:

   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. Shranite modul z enoličnim imenom in zaprite okno modula.

8. Lastnost BeforeUpdate obrazca frmAccounts nastavite na to dogodkovno proceduro:

   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. V meniju za iskanje napak kliknite »Združi <ime zbirke podatkov«>

10. Če ne pride do napak, shranite obrazec.

11. Odprite obrazec frmAccounts in nato dodajte nov zapis ali uredite zapis.
    Ko spremenite zapis, se zapis shrani, ko se premaknete na drug zapis. Če pride do napake ODBC, se prikaže sporočilo po meri, ki temelji na napaki, specifični za strežnik, in splošno sporočilo »ODBC-call failed« je ujeto.