Access 2007: Escape Out of Compacting Corrupts Database - Database1.zip (0/1)

[Stewart Berman]

Access 2007

We have a rather complicated application that has the front end set to
compact on close. During the QA process our testers were able to corrupt
the front end by hitting the escape key a few times while the front end was
compacting. Lots of nasty popup messages when the application was
restarted.

We created a simple database with one table and one form (see attached).

The form has three buttons:

Populate Data -- This empties the table and then adds a little over one
million records.

View Data -- This opens the table in normal mode.

DoCmd.Quit -- This issues a DoCmd.Quit command.

The database is set to compact on close.

To reproduce the problem do the following:
1. Open the database
2. Press the Populate Data button. This empties the table and adds a little
over one million records.
3. Press the View Data button. This opens the table in normal view. It
shows a little over one million records.
4. Close the table and press the DoCmd.Quit button. This executes a
DoCmd.Quit command and the data base compacts on close. Let it complete.
5. Open the database and press the View Data button. There are still a
little over one million records. Close the table.
6. Press the DoCmd.Quit button but this time hit the escape key repeatedly
as the database is compacting.
7. Open the database and press the View Data button. There will be less
than one million records in the table. The amount is dependent on how fast
you hit the Escape button.

I have been able to reproduce this problem on Windows 7 x64 and Windows XP
SP3 x86.

Is this a known problem and is there a way to disable the Escape key during
the compact on close?

 

[Gina Whipp]

Stewart,

AFAIK, it is a know problem in Access that that, ummm, misfeature *Compact
on Close* causes corruption. Usually caused by the database trying to
Compact while someone is still in it, FE-BE scenario. The fact that you
found and yet another way for this misfeature to corrupt a database comes as
no surprise.

It is HIGHLY recommended that you do NOT use that feature. You could
perhaps set up a batch file to run on the Server at 2:00 AM when all should
be sleeping. However, I should note that I have one Client that only
compacts the database after about a month because it hardly grows enough to
be concern. Is yours growing so fast that you feel the need to Compact
daily?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Stewart Berman]

I have been using MS Access since the first beta. Repair has always had
problems but Compact was relatively safe. All it did was create a new
database and copy all of the objects from the old one into the new one.

AFAIK, it is a know problem in Access that that, ummm, misfeature *Compact
on Close* causes corruption.

Is there anything from Microsoft about this?

Usually caused by the database trying to
Compact while someone is still in it, FE-BE scenario.

This is a single user application (open exclusive) that lives on the C:
drive.

 

[Douglas J. Steele]

I'm with Gina on this one: Compact on Close is seldom (if ever) a good idea.

For one thing, all applications (whether single-user or not) should always
be split into Front End/Back End. Compact on Close only works on the front
end, which shouldn't need compaction (if it grows too much, simply replace
it)

 

[Marshall Barton]

Because Compact is such an extremely heavy duty operation
(it rearranges nearly every byte in the file), you should
always make a backup before using Compact.

Because you can not safely copy an open database, making a
backup copy needs to be done outside the database. That
rapidly leads to the conclusion that Compact On Close is a
very dangerous operation and should NOT be used.

OTOH, it is a good idea to design your application so that
Compact is not needed on a regular basis. If the bloat is
caused by the use of make table queries, you should either
figure out how to do the job without using make table
queries or change your code to use a temprary database to
hold the temporary tables.

If the bloat is in the front end, it is probably because you
have code that makes persistent changes (i.e. modifies the
design of form/report/query objects). This kind of thing is
also a heavy duty operation that should NEVER be done during
normal use so Compact would only be used during a developer
design/coding session where a making a backup copy would be
a normal part of the development cycle.

 

[Stewart Berman]

First of all the application is split into front and back ends. However,
the work tables are in the front end and it is the front end that is the
problem. The work tables are in the front end to avoid pushing the backend
anywhere close to the 2GB limit. The data in the front end work tables can
be persisted if the user choices to pause in the middle of some operations
so simply replacing the front end would cause problems.

The answer is probably something I used a long time ago. Have the front end
monitor its size and when it starts getting too big have it launch a little
app before it closes. The app then compacts the database. Workarounds are
such fun.

 

[Stewart Berman]

There is a management function in MS Access 2007 to compact the database.
The only difference between compact on close and the management function is
that the database is not reopened when doing a compact on close. The
management function closes the database, does a compact and reopens the
database. When using compact on close the code or the user closes the
database and Access compacts it.

So if compact on close is that dangerous then the manual management function
should also be avoided which means the only save way is to have another
application run the compact. But that has the same exposure. The only
difference is that the database starts in the closed state.

So the process can consist of:

1. Closing the database
2. Compacting the database
3. Reopening the database

The management function does all three. Compact on close does (1) and (2).
Using a separate app does (2). But in all three cases when (2) is run the
database is closed.

In short compacting has the same risk no matter how it is done.

 

[RoyVidar]

First of all the application is split into front and back ends.
However, the work tables are in the front end and it is the front end
that is the problem. The work tables are in the front end to avoid
pushing the backend anywhere close to the 2GB limit. The data in the
front end work tables can be persisted if the user choices to pause
in the middle of some operations so simply replacing the front end
would cause problems.

The answer is probably something I used a long time ago. Have the
front end monitor its size and when it starts getting too big have it
launch a little app before it closes. The app then compacts the
database. Workarounds are such fun.

Another workaround, would be to use a (local?) temp database for the
local "work tables" so that they are not lokated in the actual front
end. This temp database could be recreated per each session, day,
week... a given size or whatever you choose, and will live (or not)
independent of say replacing the front end.

 

[Gina Whipp]

Stewart,

I see ther has been ALOT of activity since I last posted and I have read
them all... Here's my two cents worth. Why not have a Temp Database to
handle all the work, you can link to that file. After it's done all the
work, push the data to the Live backend and *Kill* the Temp database.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Douglas J. Steele]

In short compacting has the same risk no matter how it is done.

True, but only performing compacts when required minimizes that risk.

 

[Douglas J. Steele]

Tony Toews has an example of using a temporary database for temporary tables
at http://www.granite.ab.ca/access/temptables.htm

 

[David W. Fenton]

There is a management function in MS Access 2007 to compact the
database. The only difference between compact on close and the
management function is that the database is not reopened when
doing a compact on close.

Wrong.

The key difference between the two is that compacting from the menu
is not something that happens automatically. Compact On Close
happens without any ability to cancel. Compact from the menus/ribbon
is something that can't happen except if you explicitly initiate it.

To summarize (and emphasize) what others have posted:

1. COMPACT ON CLOSE is dangerous because it can't be cancelled.

2. COMPACT ON CLOSE is dangerous because there's no non-Byzantine
way to make a backkup before it runs, and compact should never be
initiated without a backup.

3. COMPACT ON CLOSE operates only on the database open in the Access
UI, and for any correctly-designed application, this is only the
front end.

4. A front end that needs frequent compacting is an
improperly-designed front end. Temp tables don't belong in the back
end or the front end, but in a back end designated for temporary
data. That back end won't need to be compacted, either, as it can
either be replaced with an empty copy, or recreated from scratch in
code as needed.

The only way that COMPACT ON CLOSE could be rescued as margingally
useful would be if Microsoft added a confirmation prompt.

No one who has given the matter any thought at all would use COMPACT
ON CLOSE. And, in fact, it's most dangerous to the people most
likely to use it, novice Access users. Like the Name AutoCorrect
misfeature, this is something that Microsoft really ought to
permanently remove from Access.

 

[Marshall Barton]

There is a management function in MS Access 2007 to compact the database.
The only difference between compact on close and the management function is
that the database is not reopened when doing a compact on close. The
management function closes the database, does a compact and reopens the
database. When using compact on close the code or the user closes the
database and Access compacts it.

So if compact on close is that dangerous then the manual management function
should also be avoided which means the only save way is to have another
application run the compact. But that has the same exposure. The only
difference is that the database starts in the closed state.

So the process can consist of:

1. Closing the database
2. Compacting the database
3. Reopening the database

The management function does all three. Compact on close does (1) and (2).
Using a separate app does (2). But in all three cases when (2) is run the
database is closed.

There is more than a few features in Access that are
unwise/dangerous and should not be used. The issue with
Compact, however it is initiated, is that you really need to
have a current backup before you use it.

I see elsewhere where you said your justification for using
compact is twofold, you are importing into front end temp
tables and your faith that everything in Access is safe.
The latter is patently false and the former should be done
using a temp daabase (and you can trap the few potential
errors creating a temp database).

 

[Stewart Berman]

2. COMPACT ON CLOSE is dangerous because there's no non-Byzantine

way to make a backkup before it runs, and compact should never be
initiated without a backup.

The way compact works is to make a new database and import all of the old
database into it. If the import succeeds the old database is deleted and
the new one rename to the old name.

The only way that COMPACT ON CLOSE could be rescued as margingally
useful would be if Microsoft added a confirmation prompt.

Actually, that is the bug in the compact on close process is that you can
cancel the process by pressing the Escape key. The problem is that instead
of deleting the new database and leaving the old one the cancel process
appears to simply stop the importing but still deletes the old database and
renames the new one to the old name.

 

[Charles Wang [MSFT]]

Hi Stewart,
I am afraid that this is a product limitation currently. If you are very
concerned with this feature, please submit a feedback to our product team
at
http://office.microsoft.com/en-us/suggestions.aspx?sitename=CL100570041033&t
ype=0

Currently please avoid pressing Escape key when doing a compact and close.

Best regards,
Charles Wang

 

[David W. Fenton]

The way compact works is to make a new database and import all of
the old database into it. If the import succeeds the old database
is deleted and the new one rename to the old name.

I would not say that it's an "import" -- Jet/ACE rewrites the
database file structures, which is not exactly the same thing as an
import.

Actually, that is the bug in the compact on close process is that
you can cancel the process by pressing the Escape key. The
problem is that instead of deleting the new database and leaving
the old one the cancel process appears to simply stop the
importing but still deletes the old database and renames the new
one to the old name.

Even worse!

 

[Stewart Berman]

I would not say that it's an "import" -- Jet/ACE rewrites the
database file structures, which is not exactly the same thing as an
import.

I think the choice of "import" was poor as a regular database import does
not bring in all of the properties and settings of the source database.
However the compacting process does create a new database first and then
populates it with the contents of the source database. The process uses
many of the same routines that a standard import would use plus some
additional ones for handling the items that the standard import does not.

It should be noted that compact requires that the source database be opened
by Access. The old JetComp utility, which is not available for Access 2007,
does not use Access to read the source database and write the compacted one.

 

[David W. Fenton]

It should be noted that compact requires that the source database
be opened by Access. The old JetComp utility, which is not
available for Access 2007, does not use Access to read the source
database and write the compacted one.

Don't you mean that it's not available for ACCDB format, not for
A2007, since MDB is a native A2007 format?