Wow. What a mess. Update queries are scary! Well, so are deletes. Append queries not so bad if you have a timestamp and can hurry and clear them out.
Don't know how many times I've said, "doh!"
I remember hearing some story about somebody who made a big mess up and was afraid he was going to get fired. It had cost a lot of money, but his boss said, "are you kidding? We just invested a lot of money in you to have that learning experience. Why would we throw that away?"
Love the attitude.
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Bill Mosca
Sent: Thursday, March 27, 2014 9:56 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Re: referential integricy sql deletion
Liz
I'll never forget the first IT job I got. All I did all day is run invoicing and do numbers analysis. Very dull, but it left me lots of time to build little utilities that saved our department tons of time. One I built was for updating groups of parent records instead of doing it one record at a time. During my first testing, I forgot to include a WHERE clause in the update query. ACK! The cascading updates updated the whole table plus messed up all the records in the child table. I thought my job was a goner for sure. When I sheepishly told my supervisor what I had done she just laughed. "Not to worry, Bill. That's just a reporting database. All the real data is in another database. We just have to reload it."
Regards,
Bill
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Liz Ravenwood
Sent: Thursday, March 27, 2014 9:42 AM
To: 'MS_Access_Professionals@yahoogroups.com'
Subject: RE: [MS_AccessPros] Re: referential integricy sql deletion
Good points Bill. It has actually saved my butt on a database where I didn't have ref integrity nor cascading deletes.
On this one, I don't know. I know in this particular code it might be okay. Items are created by coming from another table, so there is an innate backup already build as they need to match anyway.
Cool. This little piece of after update of a checkbox code will run a little faster then.
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of wrmosca@comcast.net
Sent: Thursday, March 27, 2014 9:38 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: referential integricy sql deletion
Liz
If you have set the relationship to use cascading deletes it will do it automatically. I, for one, rarely use cascading updates or deletes. I do include a bit (Yes/No) field named Active in any table where the user might want to delete a parent. I default the Active field to -1. Should the user want to delete the parent all that actually happens is Active is set to 0. All forms and reports have criteria that says Active = -1 so inactive parents don't show. That lets me keep an audit trail and not lose any records.
Another reason I don't use cascading in relationships is that it's just too easy to delete the wrong record and wipe out all the child records, especially if the record deletion warning has accidentally been turned off.
Bill Mosca
---In MS_Access_Professionals@yahoogroups.com, <liz_ravenwood@beaerospace.com> wrote :
Pros, I should know this, but well, some of my inherited databases don't have referential integrity.
However, this new cool one I'm building myself does.
If I issue an sql deletion statement from the main table, will it cascade deletes if that rule is in place or do I need to delete the subtables too?
Respectfully,
Liz Ravenwood
Programmer / Analyst
B/E Aerospace | Super First Class Environments
1851 S Pantano Road | Tucson, Arizona 85710
Office +1.520.239.4808 |
beaerospace.com
Passion to Innovate. Power to Deliver
This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (5) |
Tidak ada komentar:
Posting Komentar