Detecting SQL Server error messages in Access

[Jim Devenish]

I am developing an Access front-end linked to SQL Server back-end. In
the triggers I raise an error as in:

RAISERROR 44447 'The record cannot be changed. RI rules require a
related record in table "Locations" '

However all I get in Access when this trigger raises its error is:

"ODBC --insert on a linked table 'Tasks' failed.

Is it possible to trap the error and create my own more meaningful one?
I have tried to trap errors in the BeforeUpdate and FormError etc
events but am unable to trap it.

[Tom van Stiphout]

Re: Detecting SQL Server error messages in Access

On 9 May 2006 03:42:33 -0700, "Jim Devenish"
<internet.shopp ing@foobox.com> wrote:

ODBC-attached tables allow for quick development by Access programmers
who don't know SQL Server very well, but they also come with
limitations. You may just have found one.
Personally I do all SQL Server development using ADP (Access Data
Project), using stored procedures to insert data, and using
declarative referential integrity rather than triggers.
Trapping errors has never been a problem - refer to the Errors
collection in ADO.

-Tom.

[color=blue]
>I am developing an Access front-end linked to SQL Server back-end. In
>the triggers I raise an error as in:
>
>RAISERROR 44447 'The record cannot be changed. RI rules require a
>related record in table "Locations" '
>
>However all I get in Access when this trigger raises its error is:
>
>"ODBC --insert on a linked table 'Tasks' failed.
>
>Is it possible to trap the error and create my own more meaningful one?
> I have tried to trap errors in the BeforeUpdate and FormError etc
>events but am unable to trap it.[/color]

[Sheila]

Re: Detecting SQL Server error messages in Access

I have used RAISERROR in SQL Server triggers to write my own error
messages, but I was using an Access Project front end rather than a
regular Access database. The Project files are much more tightly
coupled to SQL Server and allow you to edit stored procedures,
triggers, and views directly rather than through Enterprise Manager.
And they report the RAISERROR messages.

You could try switching to a Project file. However, there are some
limitations. You can only attach to SQL Server tables, so if you need
to attach to two types of databases at once, you are out of luck. My
problem was with our DBAs. They would give me permissions to create and
edit tables and views, but they wouldn't give permissions to the
clients. So if you are in this situation and have clients who want to
write their own queries, you need an MS Access database.