Overriding constraint violation message

[Arvi Laanemets]

Hi

I'm at my first try to use SQL Server as back-end database for Access
front-end.

On SQL Server, I created constraints (additionally to primary key) for my
tables:
a) foreign key constraints to control data integrity (deleting entries
linked to other tables isn't allowed);
b) check constraints (certain fields or combination of fields must be
unique).

It works all fine in access, but when those constraints are triggered, the
message returned to access is a mess for an average user. Is there a way to
replace those messages with my own, p.e. like:
"Deleting isn't allowed because data are used in other tables!" or "This
name is in use!"

It looks like I can catch the error in OnError event for form, but how can I
override the error message - usual On Error routine doesn't work
(Err.Number=0), and I'm afraid whatever message I'm programming, standard
transaction error message gets displayed too.


Thanks in advance!
Arvi Laanemets

 

[Stefan Hoffmann]

hi Arvi,

It looks like I can catch the error in OnError event for form, but how
can I override the error message - usual On Error routine doesn't work
(Err.Number=0), and I'm afraid whatever message I'm programming,
standard transaction error message gets displayed too.

While you can catch them, you still need to translate them. btw, the
Err.Number should not be 0.

The problem is, that you need error handling on all forms - the On Error
event you've mentioned - and in every VBA prodedure or macro.

I would prefer a more defensive approach. Before executing such actions
check whether these actions are allowed.


mfG
--> stefan <--

 

[Arvi Laanemets]

Hi

I think, the problem is that the error doesn't occur in Access, so Access
error indicators get nothing to show - the only exception are parameters for
forms OnError event DataErr and Response. (P.e. when trying to delete a
record linked to other tables, DataErr=3146 and Response=1)

And checking all this in Acccess - it nullifies most of reasons to move data
to SQL server! I have 10+ tables, and Access query isn't good enough on
every change in data to check all other tables for existecne of related
records. So there must be some other way :-((

Let's look what happens now.
1. Access: In some form, which is based on some table on SQL server, I edit
the record, or I delete record(s);
2. Access: Access sends a transaction query request to SQL server to save
changes or to delete row(s) ;
3. SQL Server: SQL Server starts transaction to save changes or to delete
row(s) ;
4. SQL Server: Check constraint(s) for this table, and foreign constraints
for all tables are checked;
5. SQL Server: Whenever any checked constraint returns False, the
transaction is rolled back, otherwise the transaction is made;
6. SQL Server: Some information is sent to Access about transaction result;
7. Access: Access gets the information from SQL server about transaction
result;
(8. Access: Somehow I have here to catch this information, and override the
next step!)
9. Access: When transaction was rolled back, an error message is displayed.

An example of error message:

"Microsoft Office Access

ODBC -- call failed.

[Microsoft] [SQL Native Client] [SQL Server] The DELETE statement conflicted
with the REFERENCE constraint 'FK_MyTable'. The conflict occurred in
database 'MyDB', table 'dbo.MyTable', column 'MyField'. (#547) [Microsoft]
[SQL Native Client] [SQL Server] The statement was terminated (#3621)"

And now imagine, that some user who hardly knows a couple words of english
gets such message - some months after he was warned about it. And to
further confusion, there is a help button in message window, where is
explained, that probably the connection to network was lost ))


Arvi Laanemets

 

[Stefan Hoffmann]

hi Arvi,

I think, the problem is that the error doesn't occur in Access, so
Access error indicators get nothing to show - the only exception are
parameters for forms OnError event DataErr and Response. (P.e. when
trying to delete a record linked to other tables, DataErr=3146 and
Response=1)

E.g.

http://archive.baarns.com/access/faq/ad_error.asp


mfG
--> stefan <--

 

[Arvi Laanemets]

Hi again!

I did get somewhat further. OnError event has 2 parameters, DataErr and
Response. The default value for Response is acDataErrDisplay (or 1).
Changing Response to acDataErrContinue supresses the Access error message
and allows me to write my own one. I.e. something like

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Select Case DataErr
Case 3146
MsgBox "There was an attempt to delete data linked with other
tables, or to enter a duplicate name!"
Response = acDataErrContinue
End Select
End Sub


One problem remains. Error code 3146 forwarded by DataErr parameter refers
to general ODBC error "ODBC call failed". Really there are 2 types of ODBC
errors I want to override:
1) Violation of UNIQUE KEY constraint;
2) DELETE statement conflict with REFERENCE constraint.
All other errors must be processed normally.

It's sure Access gets this info from SQL server (error messages for both
cases contain such strings), but where can I find this info? Otherwise my
error message must be very general like in example above - I'd like specific
messages for both cases instead!


Arvi Laanemets

 

[Arvi Laanemets]

Hi

Thanks!

This link refers to almost same solution I found myself. But DBEngine.Errors
doesn't work for me. There exists only one item DBEngine.Errors(0) with
Number=3001 - "Invalid argument". And it doesn't depend on my actions which
caused the error (i.e. deleting linked data or entering some duplicate
value).

 

[Stefan Hoffmann]

hi Arvi,

This link refers to almost same solution I found myself. But
DBEngine.Errors doesn't work for me. There exists only one item
DBEngine.Errors(0) with Number=3001 - "Invalid argument". And it doesn't
depend on my actions which caused the error (i.e. deleting linked data
or entering some duplicate value).

Ah, I see, take a look at

http://support.microsoft.com/default.aspx?scid=kb;en-us;206175

mfG
--> stefan <--

 

[Arvi Laanemets]

Thanks!

It was what I was searching for! (Almost.)

The function works fine when I'm adding or editing a record. Unfortunatelly
it looks like MS has forgotten the case, when records are deleted - so
probably I have to find the solution myself. I haven't worked much with
recordsets per VBA, so there is a lot of reading to be done.

It looks like form's Delete event will be a right place to call a function
which will delete ODBC records. And probably I'll write a separate function
to be called from Delete event

Maybe I can get some hints:
1) how to get information, which records were marked for deletion on form
(as all my forms are continous, unlike adding/editing the record, the user
can delete several records at once)?;
2) when some records marked for deletion pass the check, and some not, is it
better to rollback the whole operation? Or is it better to handle every
marked record separately?


Arvi Laanemets

 

[Stefan Hoffmann]

hi Arvi,

It was what I was searching for! (Almost.)

The function works fine when I'm adding or editing a record.
Unfortunatelly it looks like MS has forgotten the case, when records are
deleted - so probably I have to find the solution myself. I haven't
worked much with recordsets per VBA, so there is a lot of reading to be
done.

Use the On Delete event of the form. Set Cancel to True and delete the
record manually using

CurrentDb.Execute "DELETE FROM ...", dbFailOnError


mfG
--> stefan <--