ACCESS FEED: RE: [MS_AccessPros] Re: referential integricy sql deletion

Kamis, 27 Maret 2014

RE: [MS_AccessPros] Re: referential integricy sql deletion

I worked here for 3 weeks and messed up bad. Deleted a bunch of data. So I went in to my boss and sat down and told him what I'd done and what I was doing to repair it. He smiled at me and I knew that I was working for someone who could see through screw-ups that we all do.

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Bill Mosca
Sent: Thursday, March 27, 2014 10:05 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Re: referential integricy sql deletion

That really is a great attitude. New employees should be given lots of second chances. First, they are in constant fear of messing up something. Second, they don't yet know who is out to get their job for a friend.

Regards,

Bill

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Liz Ravenwood
Sent: Thursday, March 27, 2014 10:01 AM
To: 'MS_Access_Professionals@yahoogroups.com'
Subject: RE: [MS_AccessPros] Re: referential integricy sql deletion

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.