Interesting approach for compacting on close

[Andy]

To further follow up on my last post regarding the docmd.quit vs.
Application.qui t using access 2007, I noticed that docmd.quit will correctly
compact the database (program file) if you have the "Compact on Close"
option set for the current database.

However, even with Compact on Close set, if you use the Application.Qui t
acQuitSaveNone, the compacting will not fire.

This behavior actually seems desirable, as you can now make a program
smarter upon exiting your program. If you have an exit button that performs
some housekeeping type procs, backup, etc., then exits the access program,
you can take advantage of the behavior above (docmd.quit vs.
application.qui t) to determine if you should compact.

To do this, make sure "Compact on Close" is set for the current database
(your access program file), then run the following function on your exit
button.

Hope this helps people who might have been looking for a similar solution.
This behavior works in Access 2007, but I'm not sure about 2003.
Public Function ExitButton() As Boolean
Dim progsize As Long, compactyn As Integer
progsize = FileLen(Current Db.Name)
If progsize 40000000 Then '=40 MB. Set a reasonable number where you
think the program should be compacted
compactyn = -1
Else
compactyn = 0
End If
'MsgBox "Program size is now: " & progsize & " bytes. Setting compact on
close to: " & compactyn
'Call AutomatedBU(0) 'run any of your procs, etc.
'Call CloseOpenTabs 'a routine to close out all open forms
'
Exit_btnExit_Cl ick:
'
If compactyn = -1 Then
DoCmd.Quit 'the docmd.quit will correctly run the compact on close
Else
Application.Qui t acQuitSaveNone 'For whatever reason, this command
Ignores the "Compact on Close" setting
End If
'
' Exit Function

Err_btnExit_Cli ck:
If Err = 2450 Then 'user mistankingly closed while previewing a report
Exit Function
ElseIf Err = 2501 Then
Resume Next
Else
MsgBox Err.Description & " - " & Err.Number & ", Function:
ExitButton", vbCritical
Resume Exit_btnExit_Cl ick
End If
End Function

[Rick Brandt]

Andy wrote:

To further follow up on my last post regarding the docmd.quit vs.
Application.qui t using access 2007, I noticed that docmd.quit will
correctly compact the database (program file) if you have the
"Compact on Close" option set for the current database.

Most experienced developers consider Compact-On-Close to be at best, useless
and more often, a pretty bad idea.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Andy]

I appreciate the comment and have read similar comments before, however, if
anything happens to the front-end on the compact, worst case is you grab a
new copy of the FE.

Compact on close has no affect on the BE, it only operates on the file you
have opened, not linked tables, etc.

Can you give examples of why it's a bad idea?

"Rick Brandt" <ri*********@ho tmail.comwrote in message
news:WY******** ********@flpi14 7.ffdc.sbc.com. ..

Andy wrote:

>To further follow up on my last post regarding the docmd.quit vs.
Application.qu it using access 2007, I noticed that docmd.quit will
correctly compact the database (program file) if you have the
"Compact on Close" option set for the current database.

Most experienced developers consider Compact-On-Close to be at best,
useless and more often, a pretty bad idea.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Keith Wilby]

"Andy" <PC*****@PCESof t.invalidwrote in message
news:IN******** ********@flpi15 0.ffdc.sbc.com. ..

To further follow up on my last post regarding the docmd.quit vs.
Application.qui t using access 2007, I noticed that docmd.quit will
correctly compact the database (program file) if you have the "Compact on
Close" option set for the current database.

However, even with Compact on Close set, if you use the Application.Qui t
acQuitSaveNone, the compacting will not fire.

Hmm ... I've just tried it in A2003 and it *does* work. Are you up to date
with SPs and the like?

Keith.
www.keithwilby.com

[Andy]

I'm using access 2007, with service pack 1.

Maybe Microsoft planned it so that the application.qui t with the nosave
option ignores the compact?

"Keith Wilby" <he**@there.com wrote in message
news:49******** @glkas0286.gree nlnk.net...

"Andy" <PC*****@PCESof t.invalidwrote in message
news:IN******** ********@flpi15 0.ffdc.sbc.com. ..

>To further follow up on my last post regarding the docmd.quit vs.
Application.qu it using access 2007, I noticed that docmd.quit will
correctly compact the database (program file) if you have the "Compact on
Close" option set for the current database.

However, even with Compact on Close set, if you use the Application.Qui t
acQuitSaveNone , the compacting will not fire.

Hmm ... I've just tried it in A2003 and it *does* work. Are you up to
date with SPs and the like?

Keith.
www.keithwilby.com

[Keith Wilby]

"Andy" <PC*****@PCESof t.invalidwrote in message
news:Ba******** *********@flpi1 50.ffdc.sbc.com ...

>I appreciate the comment and have read similar comments before, however, if
anything happens to the front-end on the compact, worst case is you grab a
new copy of the FE.

Why not then just grab a new copy of the FE with each new session? It's not
rocket science and has many benefits, most notably here the mitigation of
the compact on close issue.

Keith.

[Andy]

Mainly because this is a shareware runtime app that a user can download. I
think setting up a batch file, and automating that into the program
launching shortcut might be a more difficult thing in my case.

"Keith Wilby" <he**@there.com wrote in message
news:49******** **@glkas0286.gr eenlnk.net...

"Andy" <PC*****@PCESof t.invalidwrote in message
news:Ba******** *********@flpi1 50.ffdc.sbc.com ...

>>I appreciate the comment and have read similar comments before, however,
if anything happens to the front-end on the compact, worst case is you
grab a new copy of the FE.

Why not then just grab a new copy of the FE with each new session? It's
not rocket science and has many benefits, most notably here the mitigation
of the compact on close issue.

Keith.

[Rick Brandt]

Andy wrote:

I appreciate the comment and have read similar comments before,
however, if anything happens to the front-end on the compact, worst
case is you grab a new copy of the FE.

Compact on close has no affect on the BE, it only operates on the
file you have opened, not linked tables, etc.

Can you give examples of why it's a bad idea?

First off I see almost no reason a front end should ever need a compact.
Second there are cases where compacting messes up the file. For the (more
reasonable) occassional compact I would initiate it manually rather than
blindly doing it every time my app runs.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Andy]

I wish it never needed a compact. This programs is quite large fully
compacted, and makes extensive use of temp tables, which tends to bloat the
FE significantly. I even tried off-loading the temp tables to a
"workspace.accd b" local file, and it didn't solve the FE bloat. Years ago I
went over everything, and all recordsets are properly closed after they are
used, and the database variable set to nothing, so my theory has always been
the temp tables angle as the reason for the bloat.

I used to put up a message if the FE had bloated 3-4X the normal size, and
normal users would never see the message. Power users, on the other hand,
could get the message after a day or 2 of usage.
"Rick Brandt" <ri*********@ho tmail.comwrote in message
news:sz******** *********@nlpi0 65.nbdc.sbc.com ...

Andy wrote:

>I appreciate the comment and have read similar comments before,
however, if anything happens to the front-end on the compact, worst
case is you grab a new copy of the FE.

Compact on close has no affect on the BE, it only operates on the
file you have opened, not linked tables, etc.

Can you give examples of why it's a bad idea?

First off I see almost no reason a front end should ever need a compact.
Second there are cases where compacting messes up the file. For the (more
reasonable) occassional compact I would initiate it manually rather than
blindly doing it every time my app runs.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[David W. Fenton]

"Andy" <PC*****@PCESof t.invalidwrote in
news:Ba******** *********@flpi1 50.ffdc.sbc.com :

I appreciate the comment and have read similar comments before,
however, if anything happens to the front-end on the compact,
worst case is you grab a new copy of the FE.

Which accounts for the "useless" part of what Rick said.

And given the possibility of corrupting a front end (which shouldn't
ever need to be compacted in the first place), why would you want to
have to deal with helping the end user recover a working copy of the
front end if something goes wrong?

Compact On Close is a stupid feature, as badly implemented as it
could possibly be.

No one, NOT ONE USER, should ever have it turned on in any
circumstances.

But I have no strong opinions on the subject, myself.

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