Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

access deleting related records

Posted on 2013-01-23
6
Medium Priority
?
451 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 1400 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 600 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

963 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