0

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?

Community
  • 1
  • 1
pedram
  • 2,931
  • 3
  • 27
  • 43

1 Answers1

2

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;
ashareef
  • 1,846
  • 13
  • 19
  • Thanks! ArchiveDelete is a union of two actual tables. I was able to delete from Transaction from the source tables. It's two steps instead of one, but it works. Still feel there 'should' be a better solution out there, somewhere. – pedram Jul 12 '13 at 16:51
  • Unfortunately I think this is a limitation due to the complexity caused by the `UNION` in JET. Until Access I haven't had any trouble similar to this but maybe someone more knowledgeable in how the DBs are implemented can chime in as to why. Also if this solved your problem completely would appreciate an accepted answer. – ashareef Jul 12 '13 at 19:02