Compact & Repair is DANGEROUS!

[Mark Burns]

We just had a case at one of our locations where a Compress & Repair
SILENTLY DESTROYED DATA!
Well, ok, not _completely silently_, but it sure didn't do what I would hope
it would have done!
We were notified of a problem (which is an interesting item in and of
itself, but that's fodder for another thread), and in attempting to resolve
the problem at the remote location we remotely took control of a local PC
there. We opened the database in Access XP and did a compact & repair. While
the compact & Repair was in process, it experienced an error of some sort,
but it DID NOT COMPLAIN OR OTHERWISE ALERT US.
The Compact & Repair appeared to succeed normally.

However, later on, after we thought we'd fixed everything else and returned
the database to production use, we were promptly called back about a missing
database object error.
Indeed, now, a table was MISSING from the database!
We found a MsysCompactError table, and in it we found 9 records, which all
indicated an error code of -1022, an ErrorDescription, ErrorRecid, and
ErrorTable.
The first record had a description of "Disk or network error." and a table
name.
The next 8 records all had an error or messages like: "The Microsoft Jet
database engine could not find the database object named
'MsysCompactError'." and a table/query name.
For the First record, the TABLE was there, but it was NOW EMPTY!!!!???
For all of the next 8 records, the table or query name displayed was
completely missing from the compacted database!!?

Dear Microsoft,
WHY DIDN'T ACCESS DISPLAY A WARNING MESSAGE OF SOME SORT??
A little dialogue like "***WARNING*** The compressed database may have lost
data! See the MsysCompactError table for more information!" would have been
MOST HELPFUL!

Better yet, when the Compress & Repair operation cannot successfully copy
all the database objects, DON'T JUST DELETE THE ORIGINAL .Mdb file! ASK US
IF WE WANT TO KEEP IT (renamed to something like "myMdbFile_Error.mdb" or
somesuch)!
The problem may have come from some outside/transient problem that, once
resolved, simply retrying the Compact & Repair will *Actually Succeed*
WITHOUT losing data!

This all occurred with Access XP operating on a 2002-format .Mdb file.
We're avoiding the headaches of moving up to Access 2003 for various
reasons, but if somebody can tell me that this problem was addressed in
Access 2003, we'll now have a significant motive to upgrade, because I sure
don't like the thought that there's a possibility of Compact & Repair
silently destroying data on us!

 

[Larry Linson]

We just had a case at one of our
locations where a Compress & Repair
SILENTLY DESTROYED DATA!
Well, ok, not _completely silently_, but
it sure didn't do what I would hope
it would have done! . . .
A little dialogue like "***WARNING***
The compressed database may have lost
data! See the MsysCompactError table for
more information!" would have been
MOST HELPFUL!

Better yet, when the Compress & Repair
operation cannot successfully copy all the
database objects, DON'T JUST DELETE
THE ORIGINAL .Mdb file! ASK US
IF WE WANT TO KEEP IT (renamed to
something like "myMdbFile_Error.mdb" or
somesuch)!

Your suggestions would make perfect sense if you could count on being able
to detect every error, and if Access automatically stored the compacted
database back into the same database name. Neither is the case:

Compact and Repair asks you in what database you want to store the result.
No responsible person, at Microsoft or elsewhere, would suggest that you
store it back into the same database name without validating that it worked,
at least, no responsible person that I know.

When you Compact and Repair is an excellent time to save a copy of the
original in your Backup folder. I usually use a name that is a combination
of the database name and the date, so I can store multiple backups in the
same folder and fin the most recent one with ease.

In the past I had a manager who was wiser than I realized. He said "There
are only two times in the life of a project to back up your work -- EARLY
and OFTEN."

Larry Linson
Microsoft Access MVP

 

[Mark Burns]

Your suggestions would make perfect sense if you could count on being able
to detect every error, and if Access automatically stored the compacted
database back into the same database name. Neither is the case:

?? huh ??
1) if Access/Jet is creating or writing to an MsysCompactErrors table,
there's a great big clue that there's a problem that somebody should hear
about - RIGHT THEN.
2) if you open a database and then select Database Utilities->Compact &
Repair in Access XP, there is no prompt for a new database name. A new .mdb
is created automatically, and C&R runs the data into it, and when done, the
current db is closed & deleted and Access rename the new .mdb as the old one
and re-opens it.
Where in that sequence is the chance for me to back up the current db in the
event of MsysCompactErrors-type problems before the original .mdb is
deleted?
(yeah, yeah, I know...before I hit "Compact & Repair"...but I don't _know_
there's necessarily a potentially data-destroying problem at that point yet,
do I?)
My point is that if Access/Jet is creating/writing to an MsysCompactErrors
table, then any default behavior to delete the original uncompacted .mdb
should STOP WITH AN ERROR MESSAGE of some sort!

Compact and Repair asks you in what database you want to store the result.
No responsible person, at Microsoft or elsewhere, would suggest that you
store it back into the same database name without validating that it
worked,
at least, no responsible person that I know.

When you Compact and Repair is an excellent time to save a copy of the
original in your Backup folder. I usually use a name that is a combination
of the database name and the date, so I can store multiple backups in the
same folder and fin the most recent one with ease.

In the past I had a manager who was wiser than I realized. He said "There
are only two times in the life of a project to back up your work -- EARLY
and OFTEN."

Heh granted.