How do I delete items from one table using a join from another in Access?

Question

I have tried this many different ways but am getting errors strange results in all of them. I have a master table of transactions: Transactions and a query ArchiveDelete that finds new transactions from a temporary table based on the LastModifyDate. I'd like to delete transactions where ConfirmationNumber in Transactions = ConfirmationNumber in ArchiveDelete.

My first attempt was simple:

DELETE Transactions.*
FROM Transactions INNER JOIN ArchiveDelete ON Transactions.ConfirmationNumber = ArchiveDelete.ConfirmationNumber;

and I received an error: 'Could not delete from specified tables.' Clicking help is useless. I have full rights to the tables. I've attempted to Google the error and one suggestion was to run this instead:

DELETE Transactions.*
FROM Transactions Where Transactions.ConfirmationNumber in (Select ConfirmationNumber from ArchiveDelete)

But this takes forever and I don't have all day for a simple delete. There are only 183 transactions I need to remove.

I also tried the Delete using Exists here: How to delete in MS Access when using JOIN's?

DELETE Transactions.*
FROM Transactions 
Where Exists(Select 1 from ArchiveDelete Where ArchiveDelete.ConfirmationNumber = Transactions.ConfirmationNumber) = True

But now it wants to delete all 47073 rows in my table, not just the 183 that match.

What am I doing wrong? Why is this so difficult?

Answer 1

I believe because ArchiveDelete is a query might be why you are having trouble. Try making a temporary table ArchiveDeleteTemp instead (at least to test) and using that instead.

DELETE Transactions.*
FROM Transactions INNER JOIN ArchiveDeleteTemp ON Transactions.ConfirmationNumber = ArchiveDeleteTemp.ConfirmationNumber;