CODEWITHSUNDEEP
winformsms-accessms-access-2007c#-2.0
user3181915
user3181915

Reputation: 33

Deleting a record without deleting related records in another Access table

I have enabled Referential Integrity in Access 2007. I want to delete a customer record from customer table but want to keep related bill details of that particular customer in another table called bill table. When I try to delete a customer record I get an error message: "The record cannot be deleted or changed because table 'bill' includes related records".

Upvotes: 0

Views: 1339

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123689

You can't have it both ways: You can either have Referential Integrity enforced, or you can allow orphaned child records to be created when the parent record is deleted (i.e., by disabling the enforcement of Referential Integrity).

As mentioned in the comment to your question, you could add a Yes/No field named [Inactive] or [Deleted] to the parent table and then use queries that only consider "active" parent records, e.g.

SELECT * FROM [Customer] WHERE NOT [Inactive]

Another possibility is to have a separate "archive" database to store deleted items. You copy all related records (parent and child) to the archive database and then delete them (child first, then parent) from the main database.

Upvotes: 2

Related Questions