Suspected database corruption - error after compacting

[GS500]

Recently, after I run a compact and repair operation on this database, I get a referential integrity error on attempting to add a new record. I then have to export all the tables to a new database, and everything works fine until after the next compact when I immediately get the error again.

The table affected is a junction table that resolves a M2M relationship.

I suspect there is a corrupted record somewhere in the table. Is there a way to check all the records and find any that may be causing that error? Or is there something else that can be causing it? I've double checked and there is no record in the table that should be causing that. It happens if I use data entry form to add the record or if I go into the table and manually try to add the 2 FK IDs from the parent tables.

The database is a multi-user split application with the back end on a shared drive and front ends on each user's PC.

Thanks!

 

[essaytee]

Recently, after I run a compact and repair operation on this database, I get a referential integrity error on attempting to add a new record. I then have to export all the tables to a new database, and everything works fine until after the next compact when I immediately get the error again.

The table affected is a junction table that resolves a M2M relationship.

I suspect there is a corrupted record somewhere in the table. Is there a way to check all the records and find any that may be causing that error? Or is there something else that can be causing it? I've double checked and there is no record in the table that should be causing that. It happens if I use data entry form to add the record or if I go into the table and manually try to add the 2 FK IDs from the parent tables.

The database is a multi-user split application with the back end on a shared drive and front ends on each user's PC.

Thanks!

Check Allen Browne's site regarding sub-queries and, in particular, NOT EXISTS. Executing this type of query should highlight any missing records or stuffed up referential integrity.

If the NOT EXIST sub-query doesn't reveal any problems, then the problem must be something else.

 

[GS500]

I ran the Not Exists query on both sides of the junction table and it didn't return any results. So I exported the data from the junction table into Excel, used the CLEAN function on all the data, copy and pasted the values over the original data and then imported it back into Access. I was able to do a compact and repair operation this time and I'm now able to add new records to the table without any errors.

I'm not sure if the CLEAN process was necessary, but I didn't think it would hurt anything. It may have just been the process of exporting to Excel and back into Access that fixed it. Always before I would just export all the tables into a new Access database, so maybe that was bringing the invalid data over to the new version?