Solved

access deleting related records

Posted on 2013-01-23
6
430 Views
Last Modified: 2013-01-24
Suppose i have a client table and a related file table.

one client many files.

They have referential integrety

If I want to delete one client record and it related files is there an easy and safe way to do this.
Do delete the referential integrity in Relationships and then delete the two files manually. Or is it cascade upwards or something?

Or is there a way to delete all related records that belong to the client with a query say, without having to change the Relationships between the tables.
Sorry this is bit half baked but its late and I should be in bed.
0
Comment
Question by:topUKlawyer
  • 3
  • 2
6 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 350 total points
ID: 38812340
IF ... you have Enable Cascade Deletes checked in the Relationships dialog, then this should happen automatically.
And I've never had an issue with this.

mx
0
 
LVL 13

Assisted Solution

by:AielloJ
AielloJ earned 150 total points
ID: 38812357
topUKlawyer:

There are two options when you create a relationship that controls what you want to do.  The first, called 'Cascade Update Related Fields' will automatically update the foreign keys in related ttables whenever the primary ID in the parent tables is changed.

The second option called, 'Cascade Delete Related Records' will delete any child records whenever the parent row is deleted.

I would refrain from changing table relationships from within your applicaiton.  There are a number of risks that practice could cause that will have you spending hours getting things to work again.

Regards,

AielloJ
0
 
LVL 75
ID: 38812384
Just to be check ... refer to image below:

mx

Cascade Delete Related Records
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:topUKlawyer
ID: 38815165
Is there a way you can have Cascade delete checked and then put some other measure on the other users (other than me) which will restrict or make it hard for them to delete records.
I know I can make the tables invisible to them (I think)
0
 
LVL 75
ID: 38815395
Well, at the Form level, you can Not Allow Deletes ...

I guess I am not understanding the goal here RE:

"If I want to delete one client record and it related files is there an easy and safe way to do this."

If you enable Cascade Deletes, then the answer is Yes.  And I see no reason to not do this.

mx
0
 

Author Closing Comment

by:topUKlawyer
ID: 38815446
thanks Experts
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question