Compact DB from code

[dds82]

Is there a VBA function that I can call to compact the currently open database?

Thanks.

 

[Junior1544]

a database can't be compacted while open... you would need code to run a external program, and close the database, then to exit the external program, and reload the database... it's not that easy...

--James
JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[dds82]

I don't care about reloading the database; I'm trying to set this up to be automatically run when nobody is using the database. Is there some sort of command I can use in an external program to compact a database?

Thanks.

 

[GGleason]

You can create a batch file with the following command:
[tt]
"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" \\MyServer\MainShare\db1.mdb /compact
[/tt]
hth,
GGleason

 

[Junior1544]

batch files don't seem to like it any time i've done that, but that is the correct way to do it if it's timed
(i seem to alwas have to make a link, then call the link from a batch file, but i've gotten used to that)

i had a problem once with an automated compact once or twice... it hung the pc.. most of the time it worked ok...

one thing to remember, if any one is in the database at the time, it'll cancel it automaticly, and wont try to repeat it... so it'll go one time without... just a couple of things to remember...

--James
JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[ide]

call the following code from the on close event procedure of your main form (or an invisible form what loads when the client starts). I use it on NT and 2000.

'**********************************************************************************
Sub sbCompactCurrentDatabase()
Dim FS, a
Dim strWrite As String, strVBFilePath As String

strVBFilePath = CurrentProject.Path & "\testfile.vbs"

strWrite = "Set objApp = WScript.CreateObject(" & Chr(34) & "Access.Application" & Chr(34) & &quot" & vbCrLf & _
"Set FS = WScript.CreateObject(" & Chr(34) & "Scripting.FileSystemObject" & Chr(34) & &quot" & vbCrLf & _
"k = Now() + TimeSerial(0, 0, 5)" & vbCrLf & _
"While k > Now()" & vbCrLf & _
"Wend" & vbCrLf & _
"On Error Resume Next" & vbCrLf & _
"strFilePath = " & Chr(34) & CurrentProject.Path & "\" & CurrentProject.Name & Chr(34) & vbCrLf & _
"strTempPath = " & Chr(34) & CurrentProject.Path & "\Tmp_" & CurrentProject.Name & Chr(34) & vbCrLf & _
&quot;If FS.FileExists(strTempPath) <> &quot; & Chr(34) & Chr(34) & &quot; Then FS.DeleteFile strTempPath&quot; & vbCrLf & _
&quot;objApp.DBEngine.CompactDatabase strFilePath, strTempPath&quot; & vbCrLf & _
&quot;If FS.FileExists(strTempPath) <> &quot; & Chr(34) & Chr(34) & &quot; Then&quot; & vbCrLf & _
&quot; FS.DeleteFile strFilePath&quot; & vbCrLf & _
&quot; FS.CopyFile strTempPath, strFilePath, True&quot; & vbCrLf & _
&quot; FS.DeleteFile strTempPath&quot; & vbCrLf & _
&quot;End If&quot; & vbCrLf & _
&quot;FS.DeleteFile &quot; & Chr(34) & strVBFilePath & Chr(34)


On Error Resume Next
Call Kill(strVBFilePath)
On Error GoTo 0

Set FS = CreateObject(&quot;Scripting.FileSystemObject&quot

Set a = FS.CreateTextFile(strVBFilePath, True)
a.WriteLine (strWrite)
a.Close

strVBFilePath = &quot;WScript.exe &quot; & Chr(34) & strVBFilePath & Chr(34)
Shell strVBFilePath

Application.Quit acQuitSaveAll
End Sub ide

 

[ide]

khmmm, khmmm...
i use that code to compact (a currently open) database client on close ide

 

[oharab]

Have a look at my FAQ: faq705-1955
It's kinda similar to IDE's but uses an external file.

B ----------------------------------
Ben O'Hara
bo104@westyorkshire.police.uk
----------------------------------