Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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)

  3. #3
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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. <img src=/S/frown.gif border=0 alt=frown width=15 height=15>

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

    Sarah

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

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

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

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

    Sarah,
    Have you tried the Form's OnError event?
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  7. #7
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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...

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

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

    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:
    <pre>Private Sub Form_Error(DataErr As Integer, Response As Integer)
    MsgBox "Error " & DataErr & " has occurred. Cannot delete this item"
    Response = acDataErrContinue
    End Sub
    </pre>

    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
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •