Search code examples
sqlms-accessinner-join

How do I delete all related records?

I'm trying to delete a user and all the related records tied to him, and I have no clue how to use the SQL INNER JOIN statement, is there any way to do something in the style of:

DELETE * FROM tblUsers, tblEnrollment, tblLinkActivities, tblFullSchedule, tblSchedule, tblLinkMedical
WHERE [IDUser] = ?

(I know that's completely incorrect)

My relationships chart looks like so:

Relationships

Would it be easier to use 6 delete commands? Or is there another command that does that? Thanks a bunch..

Solution

  • Since you already have defined relationships with referential integrity, simply set the Cascade Delete Related Records option for each relationship.

    See https://support.office.com/en-us/article/create-edit-or-delete-a-relationship-dfa453a7-0b6d-4c34-a128-fdebc7e686af#__bmcascade

    This way you only need to delete from tblUsers, all related records are deleted automatically.

    If you can't or don't want to do this, you need to run separate delete queries on the related tables before deleting the main record.