Is there something wrong with this Delete Query? 1

[ScottishFencer]

DELETE tblAC.*
FROM tblAC INNER JOIN qrySQCDuplicateAssessmentChecker ON tblAC.txtID = qrySQCDuplicateAssessmentChecker.txtID;

I'm weeding out some duplicates from tblAC using a query that filters out the excess records.

I'm getting the error message "Could not delete from specified tables"

In this table the only thing of note is that txtID and txtAC are keys.

 

[Golom]

Deleting from a join has some unusual side effects. Try this

DELETE *
FROM tblAC 
WHERE tblAC.txtID IN 
    (Select txtID From qrySQCDuplicateAssessmentChecker);

 

[ScottishFencer]

I see where you are coming from.

I tried your recommended fix and the query stopped responding - forcing me to quit the Access. I don't know if it makes much difference but this is an Access front end onto a SQL server rear. I've tried using sub queries in the past but I seem to always have this problem.

Is anyone aware of what might cause this (off topic) or suggest an alternative remedy.

 

[PHV]

Create a make table query based on qrySQCDuplicateAssessmentChecker and then do your delete joining tblAC and the newly created table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ScottishFencer]

Thanks guys.

I've tried PHV's suggestion (query below) and still no joy and still the same error message.

DELETE tblAC.*
FROM tblAC INNER JOIN GavsDupChecker ON tblAC.txtID = DupChecker.txtID;

Perhaps some sample data might help me us get to the bottom of the problem?

txtID	txtAC	txtACText	txtStatus
71010	SCS	Cattle & Sheep	
71011	SCS	Cattle & Sheep	
71012	SCO	Cattle Only	
71013	SCS	Cattle & Sheep	
71014	SCO	Cattle Only	
71015	SCO	Cattle Only

 

[ScottishFencer]

Success!

DELETE tblAC.*, tblAC.txtAC, tblAC.txtID
FROM tblAC INNER JOIN DupsChecker ON tblAC.txtID = DupsChecker.txtID
WHERE (((tblAC.txtAC) Not Like "sqc") AND ((tblAC.txtID) In (SELECT txtID FROM DupChecker)));

Thanks for yor help guys.

 

[ScottishFencer]

Oops - take out the extra two fields in the DEL section and you have an idea of what I did.