Create a button that runs "Compact and Repair Database"

  • Thread starter Thread starter ali
  • Start date Start date
A

ali

I need to create a button which will run :

Tools--> Database Utilities --> Compact and Repair Databases...
 
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.
 
(...)
You cannot programmatically compact the current database, because the shut
down for compact cannot occur while the code is running.
Click to expand...
(...)

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
 
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.
 
K.P.

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.
 
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.
Click to expand...

Or you can use the TSI SOON (Shut One, Open New) add-in:

http://trigeminal.com/lang/1033/utility.asp?ItemID=8#8
 
ali said:
I need to create a button which will run :

Tools--> Database Utilities --> Compact and Repair Databases...
Click to expand...

Do you mean the current MDB or the linked BE MDB?

If the linked BE MDB see my Backup, do you trust the users or
sysadmins? tips page for more info.
http://www.granite.ab.ca/access/backup.htm

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/
 
--
Jeff C
Live Well .. Be Happy In All You Do


Krzysztof Pozorek said:
(...)
(...)

This is truth, but this is not whole truth ;-) Following VBA code compacts
current database:

CommandBars.FindControl(id:=2071).accDoDefaultAction
Click to expand...

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
Live Well .. Be Happy In All You Do


Douglas J. Steele said:
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
Click to expand...

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
 
Jeff C said:
--
Jeff C
Live Well .. Be Happy In All You Do




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
Click to expand...
 
Back
Top