Compact Current Dabase

  • Thread starter Thread starter Jose Perdigao
  • Start date Start date
J

Jose Perdigao

How can I compact the current database programmatically? I would like create
a button, one click and then, start compact the current database.



Any Suggestions?



Thanks

josé perdigao
 
Jose,

This cannot be done properly through code. The reason is, Access cannot
compact an open database; when you run Compact and Repair from the menu,
Access closes the database first, and then compacts the closed file and
re-opens it at the end. Obviously, you can;t have code running off a
closed database!

I said "properly" because you could use DoMenuItem to do it, but it's
not advisable; for one thing, it will not be easy to get back to the
exact point where you were (open forms/reports and criteria for them,
selected records etc.), plus it doesn't look good for the user. I can't
think of any other drawback right now, all suggestions welcome!

Since you mention you want to compact the current database, I presume
this is a monolithic database (as opposed to a front-end / back-end
split design). In this case, and assuming you Access version is 2000 or
newer, your other option is to activate the Compact On Close option
(Tools > Options, tab General) so your database is compacted every time
you close it. This should be sufficient in most cases. Note: this
setting is database-specific, it will not apply on all your databases.

HTH,
Nikos
 
Dirk Goldgar said:
In Access 2000 or later, you can use the technique shown here:

http://www.mvps.org/access/general/gen0041.htm

In Access 97, the only way to do this was to use a separate utility,
external to your database; see this link:

http://www.mvps.org/access/general/gen0013.htm
Click to expand...

In Access 97 it is possible to do it programmatically by using the menu.
By putting the database into a know menu condition you can use SendKeys to
select the Compact Database menuitem.
Not elegant but it does the job without an external utility.

Andy

Andy
 
Andy said:
In Access 97 it is possible to do it programmatically by using the
menu. By putting the database into a know menu condition you can use
SendKeys to select the Compact Database menuitem.
Not elegant but it does the job without an external utility.
Click to expand...

Yes, that's true; I shouldn't have said "the only way". However, using
SendKeys is not a reliable method, because there's always the slight
possibility that some other application will grab the focus just as
you're sending your keystrokes.
 
Hmm... not good advice, I'm afraid. SendKeys is known to produce all
kinds of adverse results (because Access doesn't control where and when
the keys are sent, they are just sent blind), therefore the overall
consensus is to avoid it at all costs.

Nikos
 
Back
Top