Delete data from Table based on Select Query - (1 Viewer)

[Scaniafan]

Delete data from Table based on Select Query - Could not delete from specified table

I'm trying to delete all duplicates from a master table with autonumber field as primary key.

Based on other information found via the big Google machine, I've build a query that groups by on all fields in the master table, with a first on the AutoNumber. This results in an overview of all the lines that are not to be deleted.

Based on that query, I'm creating a query with a right join between the autonumber field in the master table and the first of autonumber in the query.

In SQL view, it looks like below:

DELETE TBL_Master_CP201.*, TBL_Master_CP201.AutoNumber
FROM QRY_CP201_Append_2 RIGHT JOIN TBL_Master_CP201 ON QRY_CP201_Append_2.FirstOfAutoNumber = TBL_Master_CP201.AutoNumber
WHERE (((TBL_Master_CP201.AutoNumber) Not In ([QRY_CP201_Append_2]![FirstOfAutoNumber])));

When I run this as a select query, it shows me the rows which I want to delete.

When I try to run it as a delete query, it gives me the error "Could not delete from specified tables".

I've searched on the forum and Google, which gives me the option to set "Unique Records" to "Yes".

Can somebody help me on this?

 

[llkhoutx]

It's probably a non-updateable recordset. Take a look at this.

 

[Scaniafan]

I didn't get it to work like you suggested, but it turned out it was faster to have the query create a new table and delete the old table via VBA.