• How to trap SQL Server errors in MDB? (2003 SP1)

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » How to trap SQL Server errors in MDB? (2003 SP1)

    Author
    Topic
    #423636

    I have an MDB with SQL Server 2000 linked tables. I need to trap errors that are caused by a rule on the SQL server table. For example, we have a foreign key relationship between a couple of the SQL server tables. When I try to delete a record in one table and there are related records in the other table, the error is displayed as shown in the attachment.

    How can I trap that error and display a more user-friendly message?

    Viewing 0 reply threads
    Author
    Replies
    • #970314

      See if ACC2000: How to Trap Specific ODBC Error Messages helps (although it’s for Access 2000, it should be valid for Access 2003 too)

      • #970335

        It sounds like it will work.

        But I added the example Error Handler routine to the OnDirty, OnDelete, BeforeDelConfirm events of the form and the SQL server error appears before any of these events happen (or so it seems). Nothing appears in the Immediate Window. frown

        Any suggestions on where else I can put the error trap?

        Sarah

        • #970337

          I’m out of my depth here. Hopefully someone else can assist.

        • #970358

          Sarah,
          Have you tried the Form’s OnError event?

          • #970385

            Yes, I tried that too. No luck. Doesn’t seem to get to that event before the SQL server error appears. This is the code I’ve used, modified for my situation.

            Private Sub Form_Error(DataErr As Integer, Response As Integer)
            On Error GoTo ODBCErrorHandler

            Exit_Sub:
            Exit Sub

            ODBCErrorHandler:
            Dim errX As DAO.Error

            If Errors.Count > 1 Then
            For Each errX In DAO.Errors
            Debug.Print “ODBC Error”
            Debug.Print errX.Number
            Debug.Print errX.Description
            Next errX
            Else
            Debug.Print “VBA Error”
            Debug.Print Err.Number
            Debug.Print Err.Description
            End If

            Resume Exit_Sub
            End Sub

          • #970386

            Correction!

            I removed the “On Error GoTo ODBCErrorHandler” part and the exit sub part as well. And now the error number was written to the debug window.

            But… It just returns the VBA Error of 0.

            So I don’t think that helps me any…

            • #970387

              Sarah,
              Sorry, having read what I wrote, I realise that it wasn’t at all clear! I don’t think the code you have will work as suggested in that article because the error is not being created by code, but by Access/Jet.
              If you use something like:

              Private Sub Form_Error(DataErr As Integer, Response As Integer)
                  MsgBox "Error " & DataErr & " has occurred. Cannot delete this item"
                  Response = acDataErrContinue
              End Sub
              

              you should see the error raised and no ODBC error. You may want to experiment to find the relevant error numbers (there must be a reference somewhere!) and handle those specifically.
              HTH

            • #970391

              Rory,
              Thanks, that worked. The message box does appear before the SQL server error. Thanks. Yes, it did return an error number of 3146, which is the ODBC call failed error.

              Just have to realize that the same message box will appear for other causes of the error 3146. So have to word the message box appropriately.

              Thanks again.

    Viewing 0 reply threads
    Reply To: How to trap SQL Server errors in MDB? (2003 SP1)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: