Compacting databases

[neoartz237]

I have my tables database separated from my app mdb and I connected them through linking.

As we all know, everytime you use the db, it bloats, so my Backend.mdb bloated upto 300+MB but then when I tried compacting it through the compact on close from Access, and it shrunk to 15MB! Nice

So how can I compact my BackEnd.mdb from my App.mdb?

As I've heard you can use the CompactDatabase() function, but it never actually compacts the original database but merely creates a compacted version of it, hence there`s a sourcepath and a destinationpath.

So How can I compact that database only?

I've tried giving the CompactDatabase only one argument for the sourcepath, but it gives errors

Please help

 

[boblarson]

...but it never actually compacts the original database but merely creates a compacted version of it, hence there`s a sourcepath and a destinationpath.

First of all, some instruction here - Compacting from the tool bar actually does the same thing. Believe it, or not, it actually creates a copy of the database and replaces the current file with the new file. When you run it from there, it has to close the database (so it can do this copy and replace); if you have a large database, keep an eye out on the directory when you run it and you will see that it creates a temporary database within the folder before replacing the old one.

Next, you can use this code

SendKeys "%(TDC)", False

to run a compact as if you were doing it from the toolbar (no names or paths needed) - I haven't used it in Access 2007 and it may not be available given the radical change from the toolbar to the ribbon, but it works in every other version.

What you need to do is make sure any objects are closed first, and if you are doing the backend, it shouldn't have any forms, etc. But you could call the code from a commandline to run and therefore compact.

 

[neoartz237]

Yep, it worked, thanks so much sir hahaha, I got your point, that Access actually creates a compacted version then replaces the origianal too, hehehe that gave lots of ideas so I coded:

Public Sub compactBackEnd()
On Error GoTo doon
Dim Fpath2, Bpath2 As String
Dim dPath, dPath2 As String
Dim f1 As File
Dim f2 As File
dPath = DLookup("Path", "tblDatalinker")
dPath2 = DLookup("Path", "tblBackupLinker")
Fpath2 = Replace(dPath, "ForEditing.mdb", "XXXXXXX.mdb")
Bpath2 = Replace(dPath2, "ForBackup.mdb", "YYYYYYY.mdb")

closeAllForms

Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(Fpath2) And Not fso.FileExists(Bpath2) Then
CompactDatabase dPath, Fpath2, ";pwd=" & getPassword, , ";pwd=" & getPassword
CompactDatabase dPath2, Bpath2, ";pwd=" & getPassword, , ";pwd=" & getPassword
fso.DeleteFile (dPath)
fso.DeleteFile (dPath2)

Set f1 = fso.GetFile(Fpath2)
Set f2 = fso.GetFile(Bpath2)
f1.Name = "ForEditing.mdb"
f2.Name = "ForBackup.mdb"
End If
GoTo ok
doon:
MsgBox Err.Description, vbCritical
ok:
End Sub


And added this code so I can close al the forms :

Public Sub closeAllForms()
Dim frmName As Object
For Each frmName In CurrentProject.AllForms
If frmName.Name <> "frmMainMenu" Then
DoCmd.Close acForm, frmName.Name
End If
Next
End Sub

Again, thank you so much goos sir and sorry for being such a newbie hehee,