Compacting Database 4

[wildcanada1]

Hi all

For awhile now I've been looking over the ways to compact a database in Access '97 and I finally found a solution that I like.

-- Create a new database called "startup.mdb".
-- Create a form with one button with the following code behind it:

Private Sub cmdCOMPACT_Click()
CompactDatabaseX
End Sub

-- And then create the CompactDatabaseX subroutine:

Function CompactDatabaseX()

Dim stappname As String

'Delete the last temporary compacted database
If Dir(&quot;compacted.mdb&quot <> &quot;&quot; Then
Kill &quot;compacted.mdb&quot;
End If

'Compact your database.
DBEngine.CompactDatabase &quot;original.mdb&quot;, &quot;compacted.mdb&quot;

'Rename your database back to &quot;original.mdb&quot;
FileCopy &quot;compacted.mdb&quot;, &quot;orginal.mdb&quot;

'Launch your newly compacted database
stappname = &quot;MSAccess.exe &quot; & &quot;original.mdb&quot;
Call Shell(stappname, 1)

'Close the StartUp Database
DoCmd.Quit

End Function

I hope this helps.

This is my first post so if anyone has any comments let me know.

Mike

 

[jimmythegeek]

You can also create a icon on your desktop with the following command line. This will open your database, repair and compact it, and close it. Use the appropriate path to your database of course (local or network).

&quot;C:\Program Files\Microsoft Office\Office\MSACCESS.EXE&quot; C:\temp\YourDBNameHere.mdb /compact /repair Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[wildcanada1]

Jim,

But lets say this database is on the network available to 10 users. The method that I posted to have it compact and startup automatically would be the right way to go I assume?

I suppose you could have the Startup database automatically run instead of having the user click the button ..

Mike

 

[jimmythegeek]

We do the compact as part of a daily process. We have three shifts, and between the day and swing shift, everyone exits the application on their way out, it gets compacted and repaired manually, and is ready for the next shift.

Obviously if there are users in the application, this will not work, as you cannot compact an open database.

If the way you do it works for you, then that is great. There is always more than one way to do things. Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[llafretaw]

This is the code that I have been seeking for ages, and trying to get the datebase to self compact is something that I really needed.Cheers

 

[NKA]

Eureka! At last! Code to compact with minimal user interaction!

I like the shortcut on desktop option as I have a backend database on the network, but the frontend is on the hard drive of individual PC's. This way, they can compact whenever they like!

Thank you - you deserve a star!

NKA

The answer's always easy - if you know it! ;-)

 

[richand1]

On this topic then people, if I have the setting of the backend to compact and repair on close and I then add the "Quit" command after an ok only message box that appears, the db does not compact and repair and then close.

Why is this and how do I get it to do this?

Thanks
Rich

 

[mp9]

Take a look at the Handy Access Launcher, a freeware utility designed with scheduling database compacts, reapirs, macro runs, etc, with minimal (i.e. no) user interaction.

HAL is freeware from

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