compact database in VBA access 2007

[SL]

Hello,

I have recently upgrade to office 2007 from office 2003. To compact a
database from within the database itself, I used the follwing code.
Unfortunately it no longer works in access 2007. Is there some similar code
that will work?

Public Function FncCompactTheCurrentDB()
CommandBars("Menu Bar"). _
Controls("Tools"). _
Controls("Database utilities"). _
Controls("Compact and repair database..."). _
accDoDefaultAction
End Function

Thank You,
SL

 

[Tom van Stiphout]

On Thu, 28 Jan 2010 17:34:01 -0800, SL <[email protected]>
wrote:

It's not recommended to compact yourself. Rather, split the database
into front-end and back-end, store the back-end on the server, and
schedule a task to compact it ever so often, for example using the
/compact switch to msaccess.exe.

-Tom.
Microsoft Access MVP

 

[SL]

I know what you mean. I have some databases where I do periodically compact
them. But in this case, I prefer to do it once in code because I have many of
these back-end databases in different locations. The data is imported and
used as a reference. The data is not likely to change.

Thanks.

 

[Douglas J. Steele]

But what you posted as working in Access 2003 only compacts the front-end,
not the back-end!

 

[SL]

Yes, but on first use of this back-end, I use it as a front-end. I import
data by running various macros, queries, VBA code all from one or more
buttons on a form. I will probably go back into it (as-required) to correct
or update the data. The last button runs this code which compacts the
database. It is very handy to have a button that will compact the database as
required rather than periodically.

SL

 

[JP]

Try it from the command line. See
http://www.codeforexcelandoutlook.com/blog/2009/06/clean-and-repair-your-access-database/
for sample code (scroll down to the comments).

--JP