Solved

access deleting related records

Posted on 2013-01-23
6
412 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 - Access MVP) 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
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.

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

777 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