error messages from ODBC

[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"

Thanks

 

[Dirk Goldgar]

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"

Normally an error in an ODBC operation will be raised in your
application as error 3146 : "ODBC--call failed.", although there are a
couple of other possibilities. Only by examining the DBEngine.Errors
collection can you determine the specific error or errors that were
actually raised by the server. However, you can set up error handling
in code to trap the 3146 error, check the DBEngine.Errors collection to
see what server-specific error was raised, and display your own message.
I'm not sure, but I wouldn't be surprised to find that you could handle
errors like this caused by the data operations of a form by setting up
an event procedure for the form's Error event and pursuing a similar
course.