asked on

VBA code to Compact and Repair Database

I have a database (ike_gr.mdb – at the final it will be ike_gr.mde) and I want to use some VBA code which will run automatically every two-three weeks and do the same as the Compact and Repair Database menu does (I don’t want to use the menu).
Please give me a code for this.
I use Access 2002.

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ExpertAudit

Dear gavrinas,

Here you can find a good explanation to your problem and you can find a free com component to help you achieve what you want...

http://www.mvps.org/access/general/gen0013.htm

The Access Web is one of the best places around for Access related material.

jadedata

Hey gavrinas!

This is the basics of some code I use in my switchboard to compact the front end file on user demand
It is located in the HandleButtonClick sub of the switchboard

DoCmd.SelectObject acTable, , True
DoCmd.Maximize
SendKeys "%TDC"

once it runs, the front end "recycles" back up to the switchboard..

regards
Jack

Jim Dettman (EE MVE)

Here's a simpler version of calling the command bar that was posted earlier (part B of the PAQ posted by ryancys). Note that part "A" for compacting a remote database is not the same as compacting a MDB from the menu bar. When you do a JET engine compact, your not fully compacting the database (The JET engine does not understand Access objects and therefore cannot compact them).

Note however that in *all* cases (part B, and solution by ExpertAudit, and my code), you cannot compact a currently open MDB, so all these methods will close, compact, and then re-open the MDB. If you can live with that, great. If not, I can suggest other approaches.

Jim.

Public Sub SelfCompact()
With CommandBars.Add(, 1, , True)
.Controls.Add 1, 2071, , , True
.Visible = True
.Controls(1).SetFocus
DoEvents
SendKeys "~"
End With
End Sub

Frédéric Métraux

----------------------------------------------------------------------------------------
This question has been abandoned and needs to be finalized.
You can accept an answer, split the points, or get a refund (information at http:/help.jsp#hs5)
If you need a moderator to help you, post a question at Community Support (http:/Community_Support/)

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

ornicar
Cleanup Volunteer

---------------------------------------------------------------------------------------------

jadedata

No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: ryancys {http:#9873352}

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

jadedata
EE Cleanup Volunteer