Question Cascade delete related records not working

[kitandy]

We have a database, created in access 2010, to manage a club’s membership records with each member having a specific membership number. The policy is to delete all details of any member who has not renewed after a specified period.
I created a query that identifies members who meet this criterion and appends their membership numbers to a table “T members to be deleted”. The membership number field in this table has a relationship to membership number in all other tables. I then created a delete query to remove all membership number in T members to be deleted. This worked a treat and deleted the appropriate records in all tables.
I have now upgraded to Access 365 and whilst the query removes the membership numbers T members to be deleted all other records remain unchanged. I have checked and all relationships are correctly set to “cascade delete related records”
Help would be much appreciated
Update Have just tried this database on access 2007 and get the same problem

 

[GinaWhipp]

Hmm, well you must mean Access 2016 or 2019 but what would really help is to see the SQL of the DELETE query. Also, just to be sure, the database is in a Trusted Location correct?

 

[kitandy]

My web research indicates that the table that initiates the cascade delete must be on the one end of one to many or one to one relationship or the cascade fails. My Table members to be deleted is on the many end . Can I change this?

 

[MajP]

My Table members to be deleted is on the many end . Can I change this?

No that would not make sense.

If in a db I have an Employee database and I have Employees (the One side) and they have children

Mr. Jones
-- Billy
-- Cindy
-- Bobby

If I delete Mr Jones from the database because he leaves the company I want to delete the children (the many side). If not i have orphans in the database. But if I delete Bobby because he is grows up and move away I would not want to delete the other children and the parent. Makes no sense.

So can you show a screen capture of the relationship window. As you describe it sounds correct. You have a parent table with Membership Information, and then child tables. However, not certain that is what you have.

 

[GinaWhipp]

Well, if MajP's answer does not clear it up then would still the SQL of the query.

 

[kitandy]

To Clarify
The delete query is successfully deleting the records form the Table members to delete. However the related records in other tables are not deleted.

Perhaps I am going about this the wrong way.

What I am trying to do is create a list of members who have not renewed and delete them from the database.

The current process is
Use an append query to create a list of membership numbers to be deleted and add it to the table Table members to delete which is empty prior to this.This table has a relationship (one on addresses to many on members to be deleted) to the table Addresses where the membership number is the primary key.

Run the detete query on the table members to be deleted which clears this table but not deleted records.
Suggestions please

 

[isladogs]

Also just to clarify, here is a diagram of 3 related tables

The left (PupilData) & middle (PupilGrades) tables are linked using RI with cascade update & cascade delete. The right table (GradeAverages) cannot be linked using RI as there is currently 'no unique index for the referenced field of the primary table'

So if I delete records from the left table (PupilData) , corresponding records are deleted from the middle table but orphaned data remains in the right table

BUT if I join the tables with both child tables linked direct to the parent table PupilData....

... RI + cascade update/delete can be imposed on both links resulting in no orphaned data being left behind.

Of course, if I delete records from either CHILD table, the parent records are NOT deleted. This is correct behaviour

Hope that helps

 

[kitandy]

The attachment shows where I think my problem is.
The table Members to be deleted will only form as the many end of a one to many relationship with T membership details which prevents cascade deleting.
If I manually delete a member from the membership deatails table all thier details are deleted as required.

 

[isladogs]

Remove the id field from your 'to be deleted' table. Its superfluous. Change the membership number to be the PK field. Relink and add RI+ CASCADE DELETE.

Or better still.... scrap the 'to be deleted' table. It is adding unnecessary complexity.
Instead use a multiselect listbox on a form containing members details with the membership number as the bound field. Add code to a button which deletes the records for the selected members.

 

[kitandy]

Hi I have tried your first suggestion (see the attached file) but still no cascade delete

 

[isladogs]

Would need to look at your database to advise further.
If you want to upload, change/remove any confidential data first.

However do try the second suggestion which is far better than creating an unnecessary table.

EDIT Just noticed that one of your tables has no primary key field. That may be relevant especially as it seems to be the table with each member's name details. Why is that on the many side of the join?

 

[kitandy]

Thanks
Re the one to many relationship. We have a membership type called joint using one membership number.. This is 2 folk living at the same address. We have to record all individual names for insurance purposes so can have two people listed with the same membership number and address.
Although possibly cumbersome the deletion process has worked fine for a couple of years but does not now.

By manually deleting one membership row from the addresses table I can confirm cascade deleting is not happening at all.
I am an enthusiastic and mostly self taught amateur who created this to help the club but am now well and truly out of my depth.
I have stripped all sensitive data out of the database and attach a copy.
Thanks in anticipation

 

[isladogs]

OK I see what you mean

I followed steps 1-4 & none of the 109 records were deleted.
Ideally this database needs redesigning from scratch as there are many issues with table design, field names etc
However, I've made some changes that may allow you to keep using it for a while longer.

1. Removed the duplicate link between 'Membership details' & 'Names & insurance details' tables as there should only be one linking route between tables
2. The 'Names & insurance details' table had no primary key. I've added ID & Membership Number as a composite PK
3. Created a new delete query qryDeleteExpiredMembers with SQL

DELETE addresses.*
FROM addresses INNER JOIN [T Delete Members to be deleted] ON addresses.[Membership Number] = [T Delete Members to be deleted].[Membership Number];

4. After running that query, the Cascade Delete appeared to have worked correctly but you need to check that

These are the records in each of your 5 linked tables - before & after deletion

So for now, use my new query for your step 4
I haven't looked at steps 5-8

If you're happy with all of this you could create a procedure to run each of steps 1-8 in turn (or a macro if you prefer)

One more thing. I looked at this in Access 2010. Tried to compact it and got an error I've never seen before

UPDATE Just googled that error and the problem was at my end.
See https://stackoverflow.com/questions/3601107/ms-access-2010-collating-sequence-not-supported-with-the-specified-file-format
I had changed the default sort order from 'General -legacy' to 'General'. Reversed that and it compacts without error

Hope this helps

 

[GinaWhipp]

New error on me also. I used *the Google* and found this...
https://stackoverflow.com/questions...-not-supported-with-the-specified-file-format

So, it looks like your solution IS the solution!

 

[isladogs]

Hi Gina
Yup. That's the same link as I posted in my previous response

 

[GinaWhipp]

Missed that link, was too focused on unusual error message.

 

[isladogs]

I remember changing that setting a week or two ago thinking that I shouldn't still be using legacy settings....now changed back to that!
What I'm not clear about is exactly how General & General-Legacy sort orders differ!

 

[GinaWhipp]

I'm not sure either. Hmm, but I think I know who to ask...

 

[isladogs]

Thanks Gina.
As a one-time MVP, you have contacts not available to me.
I'm also unsure why the MVP status isn't always renewed....

Cheers

 

[GinaWhipp]

Already sent with a link to this thread, in case someone wants to answer directly.

Well for a number of reasons, i.e. not enough postings in Forums (you know because one's *day* job gets in the way), not enough User Group participation.