Could not delete from specified tables

  • Thread starter Thread starter Ed
  • Start date Start date
E

Ed

I want to delete all records that match the where criteria, but it does not
work???
What am I doing wrong?

DELETE DISTINCTROW tblSignals.*, qrySignalsCal.EquipSigNo
FROM qrySignalsCal RIGHT JOIN tblSignals ON (qrySignalsCal.EquipSigID =
tblSignals.EquipSigID) AND (qrySignalsCal.EquipID = tblSignals.EquipID)
WHERE (((qrySignalsCal.EquipSigNo)="-"));
 
Access thinks you are attempting to delete from tblSignals and from qrySignalsCal.

Try modifying the query to tell it you only want to delete from tblSignals.

DELETE DISTINCTROW tblSignals.*
FROM qrySignalsCal INNER JOIN tblSignals
ON (qrySignalsCal.EquipSigID =tblSignals.EquipSigID)
AND (qrySignalsCal.EquipID = tblSignals.EquipID)
WHERE (((qrySignalsCal.EquipSigNo)="-"));

IF that doesn't work please post back. By the way, why are you using a RIGHT
JOIN and not an INNER JOIN. As far as I can tell from your query, an INNER JOIN
should give you the exact same results and may be a bit faster, so I changed it
to an INNER JOIN.
 
I used the QBE and got a Right join. I copied your SQL and went to Design
view and back to SQL view and the ", qrySignalsCal.EquipSigNo" was back???

Still does not work!

ed
 
Hmmm. I'm stuck. Access should not have added the field back in that way.

One thing, if you are using Access 2000 or later, turn off

- Track name AutoCorrect should be off
(Tools: Options: General Uncheck all name AutoCorrect features)

It can cause strange problems in the database.

Another method that may work is the following using subqueries in the where
clause to identify the records to delete.

DELETE DistinctRow tblSignals.*
FROM TblSignals
WHERE tblSignals.EquipSigId In
(SELECT qrySignalsCal.EquipSigID
From QrySignalsCal
WHERE QrySignalsCal.EquipSigNo = "-")
AND tblSignals.EquipID In
(SELECT qrySignalsCal.EquipID
From QrySignalsCal
WHERE QrySignalsCal.EquipSigNo = "-")


Your problem could be originating in qrySignalsCal. Is it by any chance a
totals query?

IF this still doesn't work try posting the SQL of qrySignalsCal.
 
Thanks, that worked!

ed

John Spencer (MVP) said:
Hmmm. I'm stuck. Access should not have added the field back in that way.

One thing, if you are using Access 2000 or later, turn off

- Track name AutoCorrect should be off
(Tools: Options: General Uncheck all name AutoCorrect features)

It can cause strange problems in the database.

Another method that may work is the following using subqueries in the where
clause to identify the records to delete.

DELETE DistinctRow tblSignals.*
FROM TblSignals
WHERE tblSignals.EquipSigId In
(SELECT qrySignalsCal.EquipSigID
From QrySignalsCal
WHERE QrySignalsCal.EquipSigNo = "-")
AND tblSignals.EquipID In
(SELECT qrySignalsCal.EquipID
From QrySignalsCal
WHERE QrySignalsCal.EquipSigNo = "-")


Your problem could be originating in qrySignalsCal. Is it by any chance a
totals query?

IF this still doesn't work try posting the SQL of qrySignalsCal.
Click to expand...
 
Back
Top