Compacting Access DB destroys relationships

[MightyAC]

Corrupt data in an access database seems to occur far too often. What's worse is that compacting an access 2000 or later db with corrupt data can result in the destruction of relationships.

It appears that during a compact access will remove corrupt records. If those removed records create referential integrity problems access deletes the relationship leaving orphaned relational data and missing relationships. Users can then unknowingly go on using the data with missing relationships causing all sorts of problems.

In 97 and previous it seems that the compact would fail and not kill the relationship which is a much better outcome.

Anyone know of a way to prevent access from destroying relationships during a compact?

[ADezii]

Corrupt data in an access database seems to occur far too often. What's worse is that compacting an access 2000 or later db with corrupt data can result in the destruction of relationships.

It appears that during a compact access will remove corrupt records. If those removed records create referential integrity problems access deletes the relationship leaving orphaned relational data and missing relationships. Users can then unknowingly go on using the data with missing relationships causing all sorts of problems.

In 97 and previous it seems that the compact would fail and not kill the relationship which is a much better outcome.

Anyone know of a way to prevent access from destroying relationships during a compact?

Prior to any Compact Operation, the Database in question should always be backed up.

[NeoPa]

Corrupt data in an access database seems to occur far too often. What's worse is that compacting an access 2000 or later db with corrupt data can result in the destruction of relationships.

It appears that during a compact access will remove corrupt records. If those removed records create referential integrity problems access deletes the relationship leaving orphaned relational data and missing relationships. Users can then unknowingly go on using the data with missing relationships causing all sorts of problems.

In 97 and previous it seems that the compact would fail and not kill the relationship which is a much better outcome.

Anyone know of a way to prevent access from destroying relationships during a compact?

If you're getting corrupt data any more times than would be consistent with unusual events (such as power outages or muppets turning off a PC without shutting the apps down properly first) then you should probably be looking closely at your database design and/or application.
That and your locking options on the PCs that use it.

[MMcCarthy]

In the Advanced tab in options, make sure the Default Record Locking is set to "Edited Record".

Mary

[MightyAC]

The data files are not becoming corrupt any more often than power outages, dropped network connections, hard drive problems, etc occur. However these things happen more often than I'd like.

Record locking is set correctly. But even if just one field in one record becomes corrupt the access compact process can delete the record. If the missing recored violates referential integrity access autmatically nukes the relationships that are being violated.

My problem is that I have several apps that use access as the back end and users compact the data regularly to improve performance. The installation of those apps includes a shortcut that compacts their data file. Since the compact process destroys relationships and doesn't even pop up a warning users continue using their applications without a clue that anything is wrong causing even more damage.

I have since built an application that will link to the datafile. Compact it and then check for the compact errors system table. If it exists it warns users that their data is damaged and to contact me so I can repair it or to restore from a previous days backup.

Considering this problem didn't exist prior to Access 2000 I was just hoping there was some sort of setting that could be changed to prevent Access from causing more problems.

[NeoPa]

I can't answer that question specifically, but there seems to be scope there to compact to a separate file first, then only copy the file over if there are no errors detected. This can also flag up the problem if you like, but at least it would reduce the danger you refer to drastically.