compact database in VBA access 2007

  • Thread starter Thread starter SL
  • Start date Start date
S

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
 
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
 
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.
 
But what you posted as working in Access 2003 only compacts the front-end,
not the back-end!
 
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
 
Back
Top