I'm trying to delete all the rows in tblProInfo,tblOrderAA where tblProInfo.proInfoScienceName='shalosh'
Here is the command I wrote. I get a message that it couldn't generate my query.
DELETE tblOrderAA.*, tblProInfo.*
FROM tblProInfo INNER JOIN tblOrderAA ON tblProInfo.proInfoSerialNum = tblOrderAA.orderAASerialPro
WHERE (((tblProInfo.proInfoScienceName)='shalosh'
DELETE a1, a2
FROM tblProInfo as a1 INNER JOIN tblOrderAA as a2
ON a1.proInfoSerialNum = a2.orderAASerialPro
WHERE a1.proInfoScienceName='shalosh'
The syntax is correct as per msaccess but doesn't delete records. Access delete queries cannot delete from more than one table at a time. This is an Access limitation.
Refer here to know about deleting records from multiple tables in MS-Access
If both tables are connected and referential integrity set such that deletes are cascaded then you can cause records to be deleted from both tables at the same time by simply deleting the record on the One side of the link.
In Sql Server:
The concept of deleting rows from multiple tables in a single delete statement cannot be done in sql server. There is the concept of triggers on the tables that do deletes in a cascading style.
On this line you have 3 opening brackets and 1 closing bracket:
WHERE (((tblProInfo.proInfoScienceName)='shalosh'
You have to separate the statements. You can not delete from two tables with one query
DELETE FROM tblOrderAA
WHERE tblOrderAA.orderAASerialPro IN
(
SELECT orderAASerialPro
FROM tblProInfo WHERE tblProInfo
tblProInfo.proInfoScienceName='shalosh'
)
DELETE FROM orderAASerialPro
WHERE tblProInfo.proInfoScienceName='shalosh'
