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?

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" <saberman@nospam.nospam> wrote in message 
news:pb86i5l0fmn820fedkq4q937eni8elpiee@4ax.com...
> 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? 

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.


"Gina Whipp" <NotInterested@InViruses.com> wrote:

>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?

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)

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Stewart Berman" <saberman@nospam.nospam> wrote in message 
news:r1c6i5pr8gka67s9bj3ivp0vplicfuappl@4ax.com...
>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.
>
>
> "Gina Whipp" <NotInterested@InViruses.com> wrote:
>
>>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? 

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.
-- 
Marsh
MVP [MS Access]


Stewart Berman wrote:
>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.
>
>
>"Gina Whipp" <NotInterested@InViruses.com> wrote:
>>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?

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.

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote:

>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)

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.

Marshall Barton <marshbarton@wowway.com> wrote:

>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 wrote:
> 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.
>
> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote:
>
>> 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)

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.

-- 
Roy-Vidar

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

"Stewart Berman" <saberman@nospam.nospam> wrote in message 
news:tgj7i5h8m1p7960to89eejk8h4gd0rrmkd@4ax.com...
> 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.
>
> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote:
>
>>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) 

"Stewart Berman" <saberman@nospam.nospam> wrote in message 
news:a7k7i5p4vki5f5gq163c35m5ls46roosog@4ax.com...
>
> In short compacting has the same risk no matter how it is done.
>

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

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

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

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Stewart Berman" <saberman@nospam.nospam> wrote in message 
news:tgj7i5h8m1p7960to89eejk8h4gd0rrmkd@4ax.com...
> 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.
>
> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote:
>
>>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) 

Stewart Berman <saberman@nospam.nospam> wrote in
news:a7k7i5p4vki5f5gq163c35m5ls46roosog@4ax.com: 

> 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. 

-- 
David W. Fenton                  http://www.dfenton.com/ 
usenet at dfenton dot com    http://www.dfenton.com/DFA/

Stewart Berman wrote:

>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).

-- 
Marsh
MVP [MS Access]

>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.

"David W. Fenton" <XXXusenet@dfenton.com.invalid> wrote:

>Stewart Berman <saberman@nospam.nospam> wrote in
>news:a7k7i5p4vki5f5gq163c35m5ls46roosog@4ax.com: 
>
>> 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. 

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

Stewart Berman <saberman@nospam.nospam> wrote in
news:ofiai5l99ddif2m60vskef4bm74b1h6vrs@4ax.com: 

>>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.

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. 

>>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. 

Even worse!

-- 
David W. Fenton                  http://www.dfenton.com/ 
usenet at dfenton dot com    http://www.dfenton.com/DFA/

"David W. Fenton" <XXXusenet@dfenton.com.invalid> wrote:

>Stewart Berman <saberman@nospam.nospam> wrote in
>news:ofiai5l99ddif2m60vskef4bm74b1h6vrs@4ax.com: 
>
>>>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.
>
>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.
>>>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. 
>
>Even worse!

Stewart Berman <saberman@nospam.nospam> wrote in
news:ak0ji5l7901cv66bea0sotrifcf34png1h@4ax.com: 

> 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? 

-- 
David W. Fenton                  http://www.dfenton.com/ 
usenet at dfenton dot com    http://www.dfenton.com/DFA/