How to handle constraint errors programmatically?

[Jim~C]

Hi,

I'm using Access 2003/ADP and SQL Server 2000 on Windows XP

I've got relational constraints setup in my SQL server database. So, for
example, can't delete a customer who has orders.

I've bound a form to the customers table. In datasheet view, I delete a
customer that has associated order rows. Rightly so, Access displays a
message saying I can't delete the customer because he has orders. However,
the msg is not very friendly.

I've played with Form_OnError, the CurrentProject.Connection.Errors
collection, Application.DBEngine.Errors, etc but none of these contain the
error I'm trying to handle.

How is error handling done with ADP projects connected to SQL Server when
SQL server prevents actions that violate DRI?

Thanks in advance!
Jim

 

[Alex White MCDBA MCSE]

Hi Jim,

there is two ways I can think of to solve your problem, firstly both
solutions require that any delete happens within code rather than

docmd.runcommand accmddeleterecord (I think thats the right spelling)
or from the toolbar because nether of these method will give you a chance to
see what is going on.

the 2 ideas I have are this.

1. firstly when the user clicks on 'your' delete button, your code will
check if there are any child records before trying to delete e.g.

dim adoTest as new adodb.recordset
with adoTest
.open "Select Count(*) as Total from TblOrders Where Customer_ID=" &
me.txtCustomer_ID.value, yourcursortype, yourlocktype
if .fields("Total").value >0 then
msgbox "Sorry you cannot delete this customer as they have some
linked orders!"
else
if msgbox("Are you sure you want to delete this
customer?",vbokcancel+vbquestion,"Confirmation!")=vbok then
currentproject.connection.execute "Delete from TblCustomer Where
Customer_ID=" & me.txtCustomer_ID.value
end if
end if
.close
end with

of course what you are looking for is Total=0 (ok to delete)


by the way the first way is the way I would do it.


2. look inside the ado error object and try and work out what error occured
so that you can report to the user.

The first way should be the correct way as your are using program logic to
control what is going on, rather than waiting for an error to occur.

Hope it helps.

 

[Jim~C]

Alex,

Thanks for your response.

I've tried your 2nd idea, but for some reason, the ADO error object contains
0 errors for this situation (i.e. a constraint violation). So, there appears
to be no obvious way to detect that a constraint error has occurred - this
is what prompted me to post the question to start with. I thought that maybe
I was checking the wrong things in order to properly detect the problem and
present a friendly msg.

I appreciate your 1st idea and agree as to its validity - my complaint is
directed towards Microsoft - what good is DRI if I can't use it in my
solution? If I have to write code to enforce DRI in a friendly way, the
tools are not helping me any. Just my 2 cents. I hope you have some
influence in getting this feedback to them.

Thanks again,
Jim