Delete table's records in another database

[Don]

I have two databases. The tables in one are linked to the other.

I want to create a query in the linked database to delete all records in a
table that have duplicates. I want to delete the oldest duplicates. There
is a timestamp on all the records in the table.

I've tried building every conceivable delete query and I keep getting a
message telling me I can't delete the record.

One other thing. The database that holds the acutal tables is an .mdb file
and the database with the query is an .accdb file. This is the case because
my Monarch application can only export to an .mdb file.

Any helpful direction would be appreciated.

 

[Michel Walsh]

A query using an aggregate is not updateable (so, cannot delete either),
unless that aggregate is in a sub-query of the main WHERE clause. You didn't
supply your SQL statement, it is hard to pin point the problem, to know what
you use, etc.


On the other hand,


DELETE *
FROM tableName AS a
WHERE EXISTS( SELECT *
FROM tableName As b
WHERE a.categoryID = b.categoryID
AND a.dateTimeStamp < b.dateTimeStamp )


as example, ***could*** work. Its supporting idea is that the subquery
returns something if the actual a.dateTimeStamp is NOT the most recent one
and if so, there is some record, b, with a more recent b.dateTimeStamp, ie.
b.dateTimeStamp > a.dateTimeStamp. In that case, delete a.*


As usual, MAKE A BACKUP before doing any experimentation on valuable data.



Vanderghast, Access MVP