John Colby
jwcolby at ColbyConsulting.com
Fri Feb 3 12:20:52 CST 2006
>Cascading deletes are definitely not for DIY users but properly implemented, they save a lot of code! ROTFL, yes they do. OF course who are the intended target of Access if not DIY users? And if (large chunks of) records are disappearing, what ONE item can rip the guts out of your database? I am not talking one record here and there, I am talking about dozens, Hundreds, even THOUSANDS of records from dozens of tables. IMPROPERLY IMPLEMENTED cascade deletes can TRASH the database. All to "save a lot of code". Hmmmm..... I am not scared, I just know that users ignore prompts. Gustav, I have sat looking over my users shoulders as they hit a prompt and just click "yes". I ask them what they just did and they always say "I don't know". I am not talking to you Gustav, but you always get up in arms as if I were. I keep reminding people of this because too many developers just ROUTINELY set a relationship and then set cascade delete true. Then wonder why, when a user, knowingly or unknowingly, deletes a record and doesn't even read the "are you sure" prompt but just clicks yes, loses half his database. Many perfectly competent developers just create a relationship and set cascade delete as a natural part of the relationship. After all, you must make sure you don't have orphans, right? The bottom line is that if I set a cascade delete true, then a user can delete those child records without understanding that they are doing so. A power user can write a query to delete an client and all the child items are gone. And then don't understand how records disappear! The answer is simple, in the hands of the majority of users, cascade deletes spell disaster. If I do NOT set that cascade delete, then the power user writes his query and when he tries to delete the client, he is told no, he has to go delete the invoices and service records, and shipping records etc. He now KNOWS what the consequences of deleting an client are. He can still delete them, but he has to do so intentionally, child table by child table. Suddenly he understands that it is also shipping he is affecting, the service department, and so forth. So Gustav, and Charlotte, and anyone else out there that wants to use cascade deletes, have fun, and keep your backups handy. John W. Colby www.ColbyConsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust Sent: Friday, February 03, 2006 11:20 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Missing records I have to agree with you, Gustav. Cascading deletes are definitely not for DIY users but properly implemented, they save a lot of code! Charlotte Foust -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock Sent: Friday, February 03, 2006 8:01 AM To: accessd at databaseadvisors.com Subject: Re: [AccessD] Missing records Hi John Oh no, why are you beating this horse again? Cascade delete is a very powerful and useful feature. However - just like fire - implement it only when you know what you are doing. Your example with invoices is bad. You never delete an invoice, and if you do (it might be a draft only) you will wish to delete all its invoice lines too. If you don't, you will have orphaned invoice lines, or you will have to bother the user with deleting line by line until the draft is empty and can be deleted. You may program your own routines to be fired at a click of the supervisor's button, but doing so is somewhat similar to building unbound forms in Access. As I wrote last time: You know all about this - normalization, relations and so on - so why be so scared? /gustav >>> jwcolby at ColbyConsulting.com 03-02-2006 16:33:46 >>> John, The first thing to do is examine the relationships for the tables missing records. If "cascade delete" is turned on (a NO-NO in my humble opinion) then deleting a single record (such as a customer) will (after a warning of course) delete ALL child/grandchild/...greatgreatgreat records. Let's say that a client has invoices and invoice line items. Yep, all gone. Repair service calls / line items? Yep, all gone. Payments? Yep, all gone. Cascade delete of a single (for example) customer record WILL delete all child records, however far down they go. Potentially dozens or even thousands of records, all gone because the user was "just deleting a single customer record". I pretty much design my databases to never turn on Cascade delete, and then build delete queries tied to buttons which only supervisors can see/click. John W. Colby www.ColbyConsulting.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com