capturing error messages from ODBC connection from Access XP to SQL Server2000

[JOSEPH YOONG]

I have forms, queries,macros, VB6, etc being created in MS
Access XP.

However, the tables are all created in SQL SErver 2000
Databases systems.

In order for my MS Access XP application to work, I linked
this Access DB to all the tables in SQL Server 2000 via
ODBC connection via DSN.

The above works fine, no problem.

When I insert a duplicate record, the ODBC will give an
error message to the users which looks something like as
shown below :

[Microsoft][ODB SQL Server Driver][SQL Server] violation
of PRIMARY KEY constraint 'aaaaaA10 Salutation_PK'. Cannot
insert duplicate key in object 'A10 Salutation'. (#2627)
[Microsoft][SQL Server Driver][SQL Server] The statement
has been terminated. (#3621)

I want you advise on how I can capture this error and then
display to users of my system a friendlier message such as

"Sorry! You have entered a duplicate record. Please rekey
the data again"

Can anyone lend me some advise??

Thanks

 

[Reid]

I have been looking for a solution to this for years, but have never found
one. The only way I can think of is to create a function that will check for
the existent of an existing record with the current key value(s). If it
returns TRUE, then this record will be a dup. Sorry, but unless you're going
to use ADO or stored procs, there's little alternative.