Automating Compact/Repair Database

[MikeAngelastro]

Hi,<br>
I have an Access app that doubles in size when it runs. I would like to automatically compact and repair it on exit without the user getting involved. The VB commands I found always ask the user for database names etc. When the app is open however, compacting and repairing the database using the menus works without asking for a new database name. Why wouldn't doing it in code do the same thing?<br>
<br>
Any suggestions?<br>
<br>
Thanks,<br>
<br>
Mike

 

[DougP]

Lets see the code you are using...<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[MikeAngelastro]

Hi Doug,<br>
<br>
Here is the code.<br>
<br>
Private Sub Form_Unload(Cancel As Integer)<br>
'If conversion is complete, exit application<br>
DoCmd.RunCommand acCmdCompactDatabase<br>
DoCmd.RunCommand acCmdRepairDatabase<br>
If ConversionComplete Then DoCmd.RunCommand acCmdExit<br>
End Sub<br>
<br>
Thanks,<br>
<br>
Mike

 

[Guest_imported]

Just click on tools, Options select the General Tab and place a check mark in Compact on close

 

[MikeAngelastro]

Thanks mkervin,<br>
<br>
I tried your suggestion, but my version of Access 97 doesn't give me the option to compact on close. Is this an option on Access 2000?<br>
Thanks again.<br>
Mike

 

[Guest_imported]

Yes Mike it is and it's what I use.

 

[MikeAngelastro]

Thanks again mkervin,<br>
<br>
I have Access 2000 at home but not at work. But I will remember your tip when I use Access at home. It will be helpful.<br>
<br>
Thanks again,<br>
<br>
Mike

 

[elizabeth]

Maybe you could try providing the user's responses with SendKeys. I haven't used it in a long time but I do remember one snag: send the data to be entered <i>before</i> you send the SendKeys command.<br>

 

[MikeAngelastro]

Hi Elizabeth,<br>
<br>
Here is the code I tried but the application does an illegal operation and then quits when it tries to execute the &quot;compact&quot; command.<br>
<br>
Private Sub Command18_Click()<br>
SendKeys &quot;EnTel.mdb&quot;, True 'The compact from db.<br>
SendKeys &quot;{Enter}&quot;, True<br>
SendKeys &quot;EnTel.mdb&quot;, True 'The compact to db.<br>
SendKeys &quot;{Enter}&quot;, True<br>
SendKeys &quot;Y&quot;, True<br>
DoEvents<br>
DoCmd.RunCommand acCmdCompactDatabase<br>
SendKeys &quot;EnTel.mdb&quot;, True<br>
SendKeys &quot;Y&quot;, True<br>
DoCmd.RunCommand acCmdRepairDatabase<br>
End Sub<br>
<br>
Are there any changes I would have to make to get it to work? Does the fact that a form is loaded cause Access to perform an illegal operation? It fails with or without the &quot;DoEvents&quot; command.<br>
<br>
Thanks again.<br>
<br>
Mike

 

[elizabeth]

I don't know if this makes any diff since it fails on the compact, but if you are current with your service packs, you no longer need to use Repair. SR1 or SR2 included a new .dll that made the Compact command repair as well as compact.

 

[MikeAngelastro]

Elizabeth,<br>
I have SR2.<br>
Thanks,<br>
Mike

 

[billheath]

Was there an answer to auto compacting a 97 database without the user being involved? Doing it over the network at 2:am doesn't appear to be the best answer.&nbsp;&nbsp;Thanks for everyone's help.

 

[maverick]

Hello Everyone,

Just to further complicate this Post,
I thought I would add that if you have a &quot;FrontEnd & BackEnd&quot; type database.
The &quot;FrontEnd&quot; can be Compacted just by an Update.

But;
How do you Compact the &quot;BackEnd&quot; when everyone or the Last Person is off of the database ?????

Thanks,
Maverick

 

[billheath]

This worked in Access 97:

Public Sub compactDB()

SendKeys (&quot;C:\My Documents\DB.mdb&quot; & &quot;{Enter}&quot, True 'he compact from db
SendKeys (&quot;C:\My Documents\DB.mdb&quot; & &quot;{Enter}&quot; & &quot;y&quot, True 'The compact to db.
'SendKeys &quot;{Enter}&quot;, False
'SendKeys &quot;Y&quot;, False
DoEvents
DoCmd.RunCommand acCmdCompactDatabase


End Sub

 

[Bastien]

heathco, where/how do I call this sub? from the form, on close? Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[mp9]

The alternative to allow scheduling compacts/repairs in v2/95/97/2000/2002 is the freeware utility, Handy Access Launcher. More details are available at

http://www22.brinkster.com/accessory/resources/001.shtml

HTH

http://www22.brinkster.com/accessory/

 

[votegop]

I use a freeware called CAL2000 (Compact-a-List) from Peter DeBaets at

http://www.peterssoftware.com/cal.htm

It will repair, compact, and backup your .mdb. You can even then set MS Task Scheduler to run it automatically, say...at 2am, to do this while everyone else is logged out.

I would caution you to set up your database to automatically log-off idle users after a certain period of time, or some other method to ensure it happens only when all users are off-line.

Hope this helps.

Jay