Key violations ? - Compact and Repair ! (1 Viewer)

[BeeJayEff]

An interesting situation :
I was trying to append 1632 records from one table to another (which already had 6822 records in it). No Primary key in the new data table, but two foreign keys in it.
The query is : INSERT INTO SalesRecords SELECT ImportJunAug15.* FROM ImportJunAug15;
The first time I tried, I got 1451 key violations. Having spent time failing to find the problem, I Compacted and Repaired the db (without having changed any of the data) and tried again. Result : 1138 errors. C&R again, then 869 errors. Next time 640, and then after a final C&R it was down to 5, all of which I could trace to invalid foreign keys.

So this might be a useful lesson for others who encounter key violations - keep C&Ring the db !

Of course I would love to know what's going on here - any ideas ?

 

[gemma-the-husky]

I struggle to believe it's C&R issues.

might there be duplicates or collisions.


duplicates in the source data
collisions in the destination data.

 

[BeeJayEff]

I struggle to believe it's C&R issues.

might there be duplicates or collisions.

Me too ! No other users were accessing those tables, and I didn't change any data, just C&R and try again, repeatedly. And this was only my FE which I C&R'd (the source data was in my FE, the destination in the BE). Puzzling. Incidentally, when I C&R'd the BE a few days ago it went from 50MB down to 22MB, with a notable improvement in performance. Is that a big db in Access terms ?