You cannot programmatically compact the current database, because the shut
down for compact cannot occur while the code is running.
Microsoft even provides a way to do it:
RunCommand acCmdCompactDatabase
but it can't work for the reason explained above.
You can try stuffing keystrokes into the keyboard buffer with SendKeys. I've
no idea if that's possible, but I could not recommend it. That key sequence
could do something undesirable in a future version of Access.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"ali" <ali@discussions.microsoft.com> wrote in message
news:C6CBDD7C-D9CE-4D4B-9100-CC387F51A7C5@microsoft.com...
>I need to create a button which will run :
>
> Tools--> Database Utilities --> Compact and Repair Databases...
>
> ----------------------------------------------------------------------
>
> Thank you so much !
>
> --
> Allen Phailat Wongakanit
> You cannot programmatically compact the current database, because the shut
> down for compact cannot occur while the code is running.
>
> Microsoft even provides a way to do it:
> RunCommand acCmdCompactDatabase
> but it can't work for the reason explained above.
>
> You can try stuffing keystrokes into the keyboard buffer with SendKeys. I've
> no idea if that's possible, but I could not recommend it. That key sequence
> could do something undesirable in a future version of Access.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "ali" <ali@discussions.microsoft.com> wrote in message
> news:C6CBDD7C-D9CE-4D4B-9100-CC387F51A7C5@microsoft.com...
> >I need to create a button which will run :
> >
> > Tools--> Database Utilities --> Compact and Repair Databases...
> >
> > ----------------------------------------------------------------------
> >
> > Thank you so much !
> >
> > --
> > Allen Phailat Wongakanit
>
>
I just tried this in Access 2007 SP1.
Access replied with this dialog:
You cannot compact the open database
by running a macro or Visual Basic code.
Instead of using a macro or code,
click the Microsoft Office Button,
point to Manage, and then click
Compact and Repair Database.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Krzysztof Pozorek [MVP]" <access@vis.pl> wrote in message
news:eIdz18uQIHA.280@TK2MSFTNGP03.phx.gbl...
> (...)
>> You cannot programmatically compact the current database, because the
>> shut down for compact cannot occur while the code is running.
> (...)
>
> This is truth, but this is not whole truth ;-) Following VBA code compacts
> current database:
>
> CommandBars.FindControl(id:=2071).accDoDefaultAction
>
> K.P.
> www.access.vis.pl
Okay, it looks like this works in previous versions, but not A2007.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
news:%23f6j2HwQIHA.4740@TK2MSFTNGP02.phx.gbl...
> Thanks for the suggestion.
>
> I just tried this in Access 2007 SP1.
> Access replied with this dialog:
>
> You cannot compact the open database
> by running a macro or Visual Basic code.
>
> Instead of using a macro or code,
> click the Microsoft Office Button,
> point to Manage, and then click
> Compact and Repair Database.
>
> "Krzysztof Pozorek [MVP]" <access@vis.pl> wrote in message
> news:eIdz18uQIHA.280@TK2MSFTNGP03.phx.gbl...
>> (...)
>>> You cannot programmatically compact the current database, because the
>>> shut down for compact cannot occur while the code is running.
>> (...)
>>
>> This is truth, but this is not whole truth ;-) Following VBA code
>> compacts current database:
>>
>> CommandBars.FindControl(id:=2071).accDoDefaultAction
>>
>> K.P.
>> www.access.vis.pl
In all the threads I've read on Compact and Repair, this is the first time
I've ever seen that solution.
--
Don''t forget to rate the post if it was helpful!
email address is invalid
Please reply to newsgroup only.
"Krzysztof Pozorek [MVP]" wrote:
> (...)
> > You cannot programmatically compact the current database, because the shut
> > down for compact cannot occur while the code is running.
> (...)
>
> This is truth, but this is not whole truth ;-) Following VBA code compacts
> current database:
>
> CommandBars.FindControl(id:=2071).accDoDefaultAction
>
> K.P.
> www.access.vis.pl
>
>
>
> (...)
> > You cannot programmatically compact the current database, because the shut
> > down for compact cannot occur while the code is running.
> (...)
>
> This is truth, but this is not whole truth ;-) Following VBA code compacts
> current database:
>
> CommandBars.FindControl(id:=2071).accDoDefaultAction
I just wanted to say "THANKS" for this little piece if gold. I have a
process running every morning (Access/Office 2003) that bloats a 20 MB file
up to 130 MB+. Placing that line at the end of the process before
SetWarnings = True - compacts the database without a hitch.
You might consider changing how your application works.
From the sounds of it, you're importing data into temporary tables in your
front-end, thus causing the bloating. Instead, import into temporary tables
in a temporary database. Tony Toews has sample code at
http://www.granite.ab.ca/access/temptables.htm
"Jeff C" <JeffC@discussions.microsoft.com> wrote in message
news:3AE12910-FC6B-4A10-9F4D-CBDD9E257296@microsoft.com...
>
> I just wanted to say "THANKS" for this little piece if gold. I have a
> process running every morning (Access/Office 2003) that bloats a 20 MB
> file
> up to 130 MB+. Placing that line at the end of the process before
> SetWarnings = True - compacts the database without a hitch.
> You might consider changing how your application works.
>
> From the sounds of it, you're importing data into temporary tables in your
> front-end, thus causing the bloating. Instead, import into temporary tables
> in a temporary database. Tony Toews has sample code at
> http://www.granite.ab.ca/access/temptables.htm
>
The database and process is one built for another and myself to generate
monthly reports. I didn't see the need to split it, but am re-thinking now
that you have pointed out Tony's idea - I like that and working through the
process will help me with other things.
Thanks much for your thoughts - "Salute" Doug
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Jeff C" <JeffC@discussions.microsoft.com> wrote in message
> news:3AE12910-FC6B-4A10-9F4D-CBDD9E257296@microsoft.com...
> >
> > I just wanted to say "THANKS" for this little piece if gold. I have a
> > process running every morning (Access/Office 2003) that bloats a 20 MB
> > file
> > up to 130 MB+. Placing that line at the end of the process before
> > SetWarnings = True - compacts the database without a hitch.
>
>
>
Jeff C <JeffC@discussions.microsoft.com> escribió en el mensaje de noticias
0A7593B6-1401-43DB-BA70-6DDC2179B4B2@microsoft.com...
>
> --
> Jeff C
> Live Well .. Be Happy In All You Do
>
>
> "Douglas J. Steele" wrote:
>
> > You might consider changing how your application works.
> >
> > From the sounds of it, you're importing data into temporary tables in
your
> > front-end, thus causing the bloating. Instead, import into temporary
tables
> > in a temporary database. Tony Toews has sample code at
> > http://www.granite.ab.ca/access/temptables.htm
> >
>
> The database and process is one built for another and myself to generate
> monthly reports. I didn't see the need to split it, but am re-thinking
now
> that you have pointed out Tony's idea - I like that and working through
the
> process will help me with other things.
>
> Thanks much for your thoughts - "Salute" Doug
>
> > --
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele
> > (no e-mails, please!)
> >
> >
> > "Jeff C" <JeffC@discussions.microsoft.com> wrote in message
> > news:3AE12910-FC6B-4A10-9F4D-CBDD9E257296@microsoft.com...
> > >
> > > I just wanted to say "THANKS" for this little piece if gold. I have a
> > > process running every morning (Access/Office 2003) that bloats a 20 MB
> > > file
> > > up to 130 MB+. Placing that line at the end of the process before
> > > SetWarnings = True - compacts the database without a hitch.
> >
> >
> >
"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in
news:ey#AJruQIHA.5980@TK2MSFTNGP04.phx.gbl:
> You cannot programmatically compact the current database, because
> the shut down for compact cannot occur while the code is running.
>
> Microsoft even provides a way to do it:
> RunCommand acCmdCompactDatabase
> but it can't work for the reason explained above.
>
> You can try stuffing keystrokes into the keyboard buffer with
> SendKeys. I've no idea if that's possible, but I could not
> recommend it. That key sequence could do something undesirable in
> a future version of Access.
Or you can use the TSI SOON (Shut One, Open New) add-in:
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Question
Reply
Top
Answer
Notify me of replies