cascade delete problem

[Tonymac12]

When I delete customer info from a linked table, the customer info is removed
from the other tables in the database. I need to be able to delete old
customer info in the linked table but keep the cutomer info in the other
tables. I am using a lookup table to get customer info from the linked table.
Cascade delete is not checked. The Database is Access 97. Thanks.

 

[Tim Ferguson]

(e-mail address removed) (Tonymac12) wrote in @mb-m18.aol.com:

I need to be able to delete old
customer info in the linked table but keep the cutomer info in the other
tables.

This does not make sense. The whole point of relational integrity is to
prevent "orphanning" of records in precisely this way. You can only get
into trouble with an invoice posted to customer 108834 and no way of
knowing who that is. If the invoice is needed, then you need the customer
record. If it isn't, then delete the whole lot.

You might like to read some more about R theory and database integrity.

B Wishes


Tim F

 

[Tonymac12]

Could you or somebody recommend a creative solution to this problem? I am
stuck with the present basic design of the DB. Thanks.

 

[Rick Brandt]

Could you or somebody recommend a creative solution to this problem? I am
stuck with the present basic design of the DB. Thanks.

Add a Yes/No field that you can use to "mark" them deleted without actually
deleting them.

On new records you can filter out the "deleted" choices, but they are still
there to support historical records.