Not able to Append Records to Table
Posted on 2011-04-24
Issue: A table in our system would not append any new records. Each and every attempt to append threw a duplicate error even though the records were clearly not duplicates.
The table has been in production for about two years. At any given time it contains 15 17 million records. It has an autonum field [ID] as the primary key. There is another unique index defined on the table which is a combination of two other fields. None of the records being added (and flagged as duplicates) are duplicates based on the unique two field index.
The autonum counter in the [ID] field is up to 72 million and the total size of the file is close to but under 1GB.
According to the documentation the Autonum is unique to 4 billion and the maximum MDB size is 2GB. The table/MDB (there is only one table in the MDB) is far below either of those limits.
A compact/repair is routinely run on the table as part of routine maintenance with intervals no longer that 2 weeks.
I tried appending the records thru code and using an append query. In all cases all records were rejected with duplicate key violations.
I solved the problem as a describe below but want to understand what happened so I can monitor for it in the future.
My question is Why is each and every record being appended to the table being flagged as a duplicate?
Here is how I addressed the situation.
1. I copied the original MDB (CIOrders.MDB) as another name (CIOrders_Locked.MDB)
2. I deleted all of the records from the original table (tblOrders) in the original MDB (CIOrders.MDB).
3. I ran a compact repair on the original MDB (CIOrders)
4. LInked table (tblOrders) in MDB (CIOrders_Locked) into MDB (CIOrders).
5 Wrote an append query appending all of the records in the linked table into (tblOrders) in the original MDB (CIOrders)
6. Executed the Query
7. Problem was solved.
All of the same records are in tblOrders as were there originally. After deleting all of the records in step2. and compacting/repairing the MDB is Step 3., when I appended the records in Step 4, autonum started over.