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 <(E-Mail Removed)>
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


>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

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.

"Tom van Stiphout" wrote:

> On Thu, 28 Jan 2010 17:34:01 -0800, SL <(E-Mail Removed)>
> 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
>
>
> >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
> .
>

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

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"SL" <(E-Mail Removed)> wrote in message
news:FFE46594-C886-45CF-9F97-(E-Mail Removed)...
>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.
>
> "Tom van Stiphout" wrote:
>
>> On Thu, 28 Jan 2010 17:34:01 -0800, SL <(E-Mail Removed)>
>> 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
>>
>>
>> >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
>> .
>>

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

"Douglas J. Steele" wrote:

> But what you posted as working in Access 2003 only compacts the front-end,
> not the back-end!
>
> --
> Doug Steele, Microsoft Access MVP
> http://www.AccessMVP.com/DJSteele
> (no e-mails, please!)
>
> "SL" <(E-Mail Removed)> wrote in message
> news:FFE46594-C886-45CF-9F97-(E-Mail Removed)...
> >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.
> >
> > "Tom van Stiphout" wrote:
> >
> >> On Thu, 28 Jan 2010 17:34:01 -0800, SL <(E-Mail Removed)>
> >> 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
> >>
> >>
> >> >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
> >> .
> >>
>
>
> .
>

Try it from the command line. See
http://www.codeforexcelandoutlook.co...cess-database/
for sample code (scroll down to the comments).

--JP

On Jan 28, 8:34*pm, SL <S...@discussions.microsoft.com> wrote:
> 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