Coud Not Delete From Specified Tables (1 Viewer)

[Tskutnik]

All,
Thanks (as always for the help),

I have the following delete query giving me the "Could not delete from specified tables" error.

DELETE DISTINCTROW DuplicateGTW_4_RecordsToDelete.MaxOfData_ID, Data_Start.*
FROM DuplicateGTW_4_RecordsToDelete INNER JOIN Data_Start ON DuplicateGTW_4_RecordsToDelete.MaxOfData_ID = Data_Start.Data_ID;

The Delete query is being run from the [DuplicateGTW_4_RecordsToDelete] database.

The Target table [Data_Start] where data is to be deleted from is in the a separate linked database and separate folder called Performer/[Master.accdb]

Notable Parameters for the Data_All are:
Unique Records for [Data_Start.*] = yes.
Source Database = (current)

My guess is deleting from a linked table in a separate DB is the problem, but I don't know how to resolve. Maybe something in the Source Database parameter?

Help is appreciated.

 

[sneuberg]

I don't think you can have a join in a delete query at least I haven't been able to get them to work. You can use a subquery to specify what records to delete and then put it in a WHERE IN clause. For example let's say you create a select query that return IDs of of the records to delete then an outline of the delete query would be

DELETE
FROM NameOfTableWithRecordsToBeDeleted
WHERE ID IN (SELECT ID FROM ......)

The subquery can have joins in it.

 

[MarkK]

Also, SQL DELETE queries work on rows, so you never have to specify fields...

DELETE FROM table WHERE condition

 

[The_Doc_Man]

Add to that the use of DELETE DISTINCT. I'm not sure DISTINCT works in any action query context.