Compacting on the fly?

[VBAhole]

My application has a rather annoying habit of ballooning in size after intense use. I think this may be caused by building and manipulating tables during runtime. Is there anyway to automatically compact upon exiting? Is there any way to attach VBA coding to a control button to run the compact process?

 

[Talismanic]

I love the user name!

You can do it with a command button like this:

Dim intQuestion As Integer


intQuestion = MsgBox("Compacting the database requires that" & vbCrLf & _
"no one else be in this database..." & vbCrLf & " " & vbCrLf & _
"Are you sure you want to continue?", vbYesNo, "Warning!")

If intQuestion = vbYes Then

SendKeys "%(TDC)", False

Else
Exit Sub
End If

 

[VBAhole]

Humm, that didn't work. It didn't cause an error but it didn't compact either. What does the {%TDC} represent? Is that some specific keystroke combo for compacting?

It seems like Access would have made this process a little easier. From what I have read from the help files you can write:

docmd.RunCommand acCmdCompactDatabase

But you can only do this on a db that is not open!! What good is that.

 

[Talismanic]

Yes it is basicaly a Send Key routine - T(ools) D(atabase) C(ompact).

Not sure why it isn't working for you though. Anybody else have an idea?

 

[VBAhole]

Got it to work using:

SendKeys {f10}
SendKeys {TDC}

Thanks for the help

 

[aziz rasul]

It would be nice if there was a way of putting this code behind an "Exit Database" button which would compact the database and close the database at the same time.

 

[Peter D]

Here's something that might help:

In Access 2000 you can check the "Compact on close" option.

Also...

"Compact A List" is a free MS Access database that allows you to backup, repair, and compact a saved list of Access database files. It also saves the results of these actions in a table for later viewing, making it suitable for batch or scheduled execution.
http://www.peterssoftware.com/cal.htm

Also, there's a nice utility for scheduling compacting operations from Calvin Smith called "CompactIt" at:
http://home.sprintmail.com/~calvinsmith/compact.htm

You can also use the TSI SOON utility to close a database, compact it, and then open it again. You can also use this utility to compact on close for Access 97:
http://www.trigeminal.com/lang/1033/utility.asp?ItemID=8#8

Here's some more information about reducing database size:
http://www.peterssoftware.com/t_dbsize.htm

Hope this helps,

--
Peter De Baets
Peter's Software - MS Access Tools - http://www.peterssoftware.com