Compact on Close, set up via VBA ?

M

mscertified

I'm using the 'compact on close' option for a large database that regularly
imports extra data.
The problem is it compacts on every close (and takes a while to do so) and I
really only want it to do this if the user performed certain operations.
Is it possible to programatically turn this option on or off in VBA code.
I'm using Access 2003.

Thanks.
 
6

'69 Camaro

Try:

If (fDidUserDoTheseOperations) Then
Application.SetOption "Auto Compact", True
Else
Application.SetOption "Auto Compact", False
End If

.. . . where fDidUserDoTheseOperations is a variable set whenever the user
performs those certain operations. You need to determine the most strategic
place to put this code. Perhaps in the OnClose( ) event of a form that's
always open?

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
K

Klatuu

To get the current setting for this option:
?application.GetOption("auto compact")
To set it:
Turn it off:
application.SetOption "auto compact", false
Turn it on:
application.SetOption "auto compact", True

This was found using VBA Help
 
S

SusanV

Very nice Gunny - I do a weekly table update that bloats the database to
double size, and will definitely put this to use!!

Susan
 
S

SusanV

Yes, and it works beautifully. I set autocompact false in the OnOpen of my
main menu form, and the autocompact true at the end of the function to
update my tables. One more thing that was in the back of my mind to figure
how to do when I "got time" and hadn't gotten to - thanks again!


;-D
 
Top