Delete data from multiple table

Question

I have the below query which will delete data from multiple tables

its not working

please help me :

DoCmd.RunSQL ("delete tblTesting,tblCodes,tblContract,tblScheduled,tblAbsense,tblClock from tblTesting,tblCodes,tblContract,tblScheduled,tblAbsense,tblClock where tblTesting.TestId = " & lstResults.Column(1) & " And tblTesting.Empid = " & Me.txtEmpIdSearch.Value & " And (tblTesting.Empid= tblCodes.EmpId Or tblTesting.Empid= tblContract.EmpId Or tblTesting.Empid= tblScheduled.EmpId Or tblTesting.Empid= tblAbsense.EmpId Or tblTesting.Empid= tblClock.EmpId ) ")

Answer 1

Your SQL statement is wrong. You cannot delete from multiple tables that way. See here how it is done, table by table.

Answer 2

You need to do it one table at a time, but apart from the fact that you cannot delete from multiple tables that way, you should not specify columns in the delete clause - delete is always dealing with entire rows.

Answer 3

Your deletes will have to be one table at a time - that is just how SQL works. Some tools will let you put several SQL statements together in a string:

"delete table1 where RowId=123; delete table2 where RowID=456; delete table3 where RowID=789;"

Just verified that this method does NOT work in Access. So you are looking at a separate DoCmd.RunSQL line for each table.

Unless, of course, these are all parent/child tables and you can turn on cascading deletes. Then just delete from the parent table and enjoy the magic.