Delete query not working

[Euler271]

I'm trying to delete some records in a table called T1 using the values in a second table called T2. My SQL statement is:

DELETE T1.* FROM T1 LEFT JOIN T2 ON T1.Field1 = T2.Field1 AND T1.Field2 = T2.Field2 WHERE T2.Field1 IS NULL

I'm getting error 3086 "Could not delete from specified tables."



Can anyone tell me what's going on and how to make it run?

Sorry but I found the reason. Apparently, I need to set the Unique Records property to "Yes" for the query. Now I'm wondering how to do this in VBA.

I found that I need the DISTINCTROW reserved word:

DELETE DISTINCTROW T1.* FROM T1 LEFT JOIN T2 ON T1.Field1 = T2.Field1 AND T1.Field2 = T2.Field2 WHERE T2.Field1 IS NULL

Now it works. Hope this helps somebody else.

[orange]

This w3schools sample may help.

[pbaldy]

Sorry but I found the reason. Apparently, I need to set the Unique Records property to "Yes" for the query. Now I'm wondering how to do this in VBA.

Switching between design and SQL view, compare the SQL before and after changing that property, and you should see what needs to be added to the SQL. .