VBA sub for compressing a database 1

[Wieczoreck]

Hello everybody!

Can somebody tell me how I can compress the database automatically when I close MS Access? I use MS Access 97 and need a Visual Basic Code!
Thanx for your help!!

Daniel

 

[MaeJams]

I have a small VB app which I run to compact a database periodically. It uses a utility freely available on the Microsoft website called JetComp. My app is not automatically kicked off on the close of the database, but I'm sure you could modify it to do so. I actually use a form with a single button on it. The code behind the button is as follows:

Private Sub cmdExecute_Click()

'file path and name of Jetcomp executable
Const c_strJetCompEXE = "C:\WINNT\system32\JETCOMP.exe"

'file path and name of database to be compacted
Const c_strSourceMDB = "G:\Cob_Ltr\Cob_Data.mdb"

'file path and name of temp database
'cannot compact a database onto itself
Const c_strTempMDB = "G:\Cob_Ltr\Temp.mdb"

'Run the Jetcomp utility
Shell c_strJetCompEXE, vbNormalFocus

'The sendkeys methods fill in some fields for Jetcomp
'and close it when it is finished
SendKeys "%d", True
SendKeys c_strSourceMDB, True
SendKeys "%c", True
SendKeys c_strTempMDB, True
SendKeys "%m", True
SendKeys "%x"

'After Jetcomp is done I have to delete the source
'database, copy the temp database to the source
'database name and delete the temp database
If MsgBox("Click OK to run file operations.", vbOKOnly, "Compact Tool&quot = vbOK Then
Kill c_strSourceMDB
FileCopy c_strTempMDB, c_strSourceMDB
Kill c_strTempMDB
End If

MsgBox "Operation Complete.", vbOKOnly, "Compact Tool"

End Sub


I hope this helps...good luck.
Rich

 

[Wieczoreck]

Thanx for this splendid possibility.

Greetings
Daniel