Re: ODBC error trapping in MS Access

From: Edward64 <edward.collier_at_techop.co.uk>
Date: 4 Sep 2001 07:54:46 -0700
Message-ID: <74bed22a.0109040654.555fba00@posting.google.com>

"Frank Boettger" <frank.boettger_at_t-systems.de> wrote in message news:<9n0k0o$aem$1_at_news.sns-felb.debis.de>...
> Hi everybody :-)
>
> i've been looking the whole day for a solution for the following problem
> (and obviously i didn't find any):
>
> i'm working on a oracle (oracle8i version 8.1.6) database with a ms-access
> 97 frontend. Severall tables have quite complicated restrictions for what
> values (or better combinations of values) can be inserted (updated) into the
> columns. Therefore, i've been writing several (before update-)triggers to
> ensure valid data entering. If something doesn't fit, those triggers raise a
> userdefined exception. This way it's much faster and easier to ensure valid
> data than to check with ms-access.
> So i thought, fine, one doesn't need to check within ms-access, one's
> getting an odbc error anyway, and then one could display a error message
> box. But now, in the sub Form_Error, i'm just getting those two errors:
> 1) one general "ODBC-call failed" (error 3146)
> 2) one error 7786 (which is an ms-access-error raised by the "failing"
> trigger)
> Anyway, i don't have a chance to determine which of my userdefined oracle
> errors is responsible for that. If i let access display the errors (through
> setting the response for sub Form_Error to acDataErrContinue), directly
> after this error 7786 it will display a error message box describing my
> userdefined error. That's not of much help, because i have to react
> differently on different userdefined errors.....
>
> Any ideas?
> Thanks in advance,
>
> Frank

I'm not sure if this will work, but our generic error handler (Access/SQL Server) contains the following (amended) block:

Dim errJet As Error

' Server/JET/ODBC Error messages
If DBEngine.Errors.Count > 1 Then

   For Each errJet In DBEngine.Errors

      With errJet
         strErrorValue = .Number & " " & .Description & " " & .Source
         ' Do what you want with any of these properties e.g.
         Select Case .Number
         End Select
      End With

   Next
End If

HTH Edward Received on Tue Sep 04 2001 - 09:54:46 CDT