Cascade Delete not working

[Michelle]

I have a mainform that is based upon information from related tables. All of
the relationships between the tables has referential integrity and cascade
update and cascade delete is turned on. When I go into the form to delete a
record, it does not delete the record from all tables. The cascade update
feature is working fine but the cascade delete is not working.

Please let me know what I am doing wrong.
Thanks

 

[Wayne-I-M]

Hi Michelle

Not sure why your delete is not working.
1st MAKE A COPY of your DB (sorry to shout but it's important)
Working "on the copy"
Open each table (with master) and use the Insert Subdata Sheet
Try deleteing the sub record by deleting the master and see what happens
If this works then you may need to copy the table and insert the copy as the
original

 

[Michelle]

I tried to do as you suggested by deleting from the master table but the same
thing is still occurring. It deleted it only from the Master table but not
the relating tables. Any other suggestions?

Thanks for all of your help in this matter.

 

[Wayne-I-M]

Hi I just did a google on the problem and there are so many posts it just too
much to go through.

I have never had a problem with the cascade delete so not really sure what
else to suggest. Maybe someone else will join the thread and offer some more
advice.

 

[John W. Vinson]

I have a mainform that is based upon information from related tables. All of
the relationships between the tables has referential integrity and cascade
update and cascade delete is turned on. When I go into the form to delete a
record, it does not delete the record from all tables. The cascade update
feature is working fine but the cascade delete is not working.

Please let me know what I am doing wrong.
Thanks

Open the Relationships window (in the backend, if this is a split database)
and doubleclick the join line. Is the Enforce Referential Integrity checkbox
in fact checked? If there are more than two tables, are all the relationships
enforced, and is Cascade Deletes set on all of the relationships?

John W. Vinson [MVP]

 

[Michelle]

I have 4 tables and referential integrity is turned on for all tables. I
also have cascade update and delete selected for all relationships. When I
enter in a new record all of the tables are being updated with the new
information but when I go to delete a record using a form, it only deletes it
from one table. I don't understand why it would update all tables but not
delete from all tables.

Any help is very appreciated.

 

[John W. Vinson]

I have 4 tables and referential integrity is turned on for all tables. I
also have cascade update and delete selected for all relationships. When I
enter in a new record all of the tables are being updated with the new
information but when I go to delete a record using a form, it only deletes it
from one table. I don't understand why it would update all tables but not
delete from all tables.

Any help is very appreciated.

I'm not sure I get it here!

Referential integrity will *prevent* you from adding an orphan record, but it
certainly won't cause data to be added to all tables. To get data into four
tables, you must use the form to enter data into all four tables (using
subforms, for example).

And deleting a record in the "One" side table should either cause that record
and three (or often many more) records to be deleted, all of the "child"
records related to that one-side table record.

Could you describe the four tables; indicate how they're related; what the
recordsources of the four (?) forms are; and how you're doing the deletion?

John W. Vinson [MVP]

 

[Michelle]

The four tables I have are:
Apps
--------
AppName - pk

Types
---------
TypeID - pk

Users
----------
UserID - pk

Master
----------
MasterID - autonumber - pk
UserID
TypeID
AppName
DateRequested
Description
Approved By
Owner
Password

In my relationships window, I have the master table as the many table and
the remaining 3 all have a one to many relationship with the master table.
Referential integrity (cascading) is turned on for all relationships. I have
created a form based on the master table and I enter data into the form which
updates all 4 tables.(Good). I also use the same form to go and delete a
record but it's not deleting it from all tables. It only deletes it from the
Master table. The record source for the form is Master.

Hope this helps.
Thanks again,

 

[John W. Vinson]

The four tables I have are:
Apps
--------
AppName - pk

Types
---------
TypeID - pk

Users
----------
UserID - pk

Master
----------
MasterID - autonumber - pk
UserID
TypeID
AppName
DateRequested
Description
Approved By
Owner
Password

In my relationships window, I have the master table as the many table and
the remaining 3 all have a one to many relationship with the master table.
Referential integrity (cascading) is turned on for all relationships. I have
created a form based on the master table and I enter data into the form which
updates all 4 tables.(Good). I also use the same form to go and delete a
record but it's not deleting it from all tables. It only deletes it from the
Master table. The record source for the form is Master.

Then it's working correctly.

Cascade delete causes the Many side records to be deleted when you delete a
record from the One side.

You're deleting from the Many side, not the One side.

You surely wouldn't want an application, a user, and a type to be completely
deleted from the database whenever you deleted a master record, would you!?
All the other Master records referencing that application, that user or that
type would then become invalid.

What do you EXPECT to be deleted? and why?


John W. Vinson [MVP]

 

[Michelle]

I guess I didn't understand the cascade delete functionality. I was under
the impression that if you delete a record using the masterform, that it
would delete it from all related tables. I see I was Wrong!

The reason I wanted it to be able to delete it from all tables is if I had a
case where something was entered in incorrectly, I wanted them to be able to
go into the database and delete it from all tables. For instance, if
someone entered in the wrong UserID or AppName, I wanted them to be able to
delete that information and possibly start over. I want to be able to run
reports on valid data. Is there some other way I can achieve that
functionality. I just don't want wrong data to be entered into the tables.
I understand that it deletes it from the many side but is it possible to
delete it from the one side. Please help me to understand this better.

Thanks John

 

[John W. Vinson]

I guess I didn't understand the cascade delete functionality. I was under
the impression that if you delete a record using the masterform, that it
would delete it from all related tables. I see I was Wrong!

The reason I wanted it to be able to delete it from all tables is if I had a
case where something was entered in incorrectly, I wanted them to be able to
go into the database and delete it from all tables. For instance, if
someone entered in the wrong UserID or AppName, I wanted them to be able to
delete that information and possibly start over. I want to be able to run
reports on valid data. Is there some other way I can achieve that
functionality. I just don't want wrong data to be entered into the tables.
I understand that it deletes it from the many side but is it possible to
delete it from the one side. Please help me to understand this better.

Thanks John

I'm not sure I understand.

Suppose you have a userID for a user who appears in many main table records.
If there is one main table record which has that user entered in error, you
surely don't need to *DELETE THAT USER*? If you do so, then all of the *other*
main table records referencing that user would also need to be deleted, or
would become "orphan" records because they have a link to a nonexistant user!

If you have a main table record with an incorrect UserID, you can simply open
the form and use the combo box to select the correct userID. It's not
necessary to delete anything - just correct what's there.

And... "don't want wrong data to be entered" may simply be beyond the
capabilities of ANY computer program, just due to human nature. If there is a
choice of users, or a choice of AppNames; and any one of the choices is a
legitimate entry; there is *nothing* that Access can do to prevent a user from
selecting the wrong choice. The only way around it would be to have all of the
data already selected so there is only one choice provided, the right one...
but who gets to set THAT database up!?

John W. Vinson [MVP]