Ms-access – How to delete from two tables same field name in single query

ms access

Help me,

I want to delete same filed name from two tables in single query, I Attached query but it's not working…

DELETE PersonalTrainerMasterData ,PersonalTrainerCheckboxData
  FROM PersonalTrainerMasterData INNER JOIN PersonalTrainerCheckboxData
 WHERE PersonalTrainerMasterData.ButtonName = PersonalTrainerCheckboxData.ButtonName
   and PersonalTrainerMasterData.ButtonName='" + deleteButtonForm.getButtonToBeDeleted()
                                               + "'and TabID=1";

Best Answer

A DELETE only affects one table at a time and removes all rows that are defined in the criteria. Notice the syntax description:

DELETE [table.*] FROM table WHERE criteria

You should be able to wrap two DELETE statements in a transaction. Because PersonalTrainerMasterData contains the criteria, DELETE it last. E.g. 

BEGIN TRANSACTION
   DELETE * FROM PersonalTrainerCheckboxData WHERE ...
   DELETE * FROM PersonalTrainerMasterData WHERE ...
COMMIT

If needed you could also insert logic to decide whether the DELETE should be allowed to COMMIT or if it should ROLLBACK.

Related Question