Re: How to handle constraint errors programmatically?

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

"Alex White MCDBA MCSE" <alex@xxxxxxxxxxxxxx> wrote in message
news:uTfjg6TUFHA.3840@xxxxxxxxxxxxxxxxxxxxxxx
> 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.
>
> --
> Regards
>
> Alex White MCDBA MCSE
> http://www.intralan.co.uk
>
> "Jim~C" <jdchanATnovusint.com> wrote in message
> news:eJ$$TSOUFHA.2172@xxxxxxxxxxxxxxxxxxxxxxx
>> 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 data*** 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
>>
>
>


.