Compacting a database programmatically?

[Dennis]

Is it possible to compact a database via a programmatic command? (If so,
what's the syntax please?) I ask this because I have a 750 Mb database which,
during processing, grows past the Access limit of 2 Gb. This destroyed the
coding I did on Friday. So if possible, I'd like to compact the database
periodically during processing, if that's possible.

Thanks!

 

[Paul Overway]

See help for DBEngine CompactDatabase. You'd have to do this from code
running in another database....not the one you want to compact.

 

[Dennis]

Understood. Is there ANY way to do it from within the database itself, such
as invoking a macro (which would re-start the program after it completed)?

I'm trying to bootstrap this process internally to the specifi database app.

Thanks

 

[Paul Overway]

Ideally, your tables should be in another MDB anyway...that would alleviate
the problem you encountered. You'd just use table links to the other MDB
within the one that is running the code. Then you could compact the MDB
containing the actual tables within your code (nothing should be open in
that MDB when you compact though).

 

[Dennis]

Ah yes, of course. In the end, these tables will all be in SQL anyway. I'm
prototyping the new system, so the tables are still in flux. That's the only
reason I haven't moved them already.

There's the solution. Thanks for your assistance!

Dennis

 

[GabrielR]

Call this code on the OnOpen event of your Switchboard.

Public Sub subCompact()
On Error GoTo Err_subCompact

Dim fs, f, ProjectSize, filespec
Dim strProjectPath As String, strProjectName As String

strProjectPath = Application.CurrentProject.Path
strProjectName = Application.CurrentProject.Name

filespec = strProjectPath & "\" & strProjectName

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(filespec)

ProjectSize = Round((f.Size / 1024) / 1024, 2)

If ProjectSize > 4.5 Then
Application.SetOption ("Auto Compact"), 1
Else
Application.SetOption ("Auto Compact"), 0
End If

Exit_subCompact:
Exit Sub

Err_subCompact:
MsgBox Err.Description
Resume Exit_subCompact

End Sub


I guess you have already splitted your db, do you?

In this case the Db will compact on close when the size of the file is
greater than 5 MB, you can adjust this to your preference.