compact database


I want to compact my database right before the user close the database using VBA. Compacted database has the same name as old database in the same folder. 'DoCmd.RunCommand acCmdCompactDatabase' is not option, because of user interaction and can not give the same names. In Access menu there is 'Tools->Securities->Compact Database' option, but the application starts again after the compact. This is not my expectation.
Who is Participating?
geneheadConnect With a Mentor Commented:
If I understand what you want to do, here is how I do it.

(Cut from working code)

Dim sPath, sFile, sPW as String
sPath = "C:\MyPath\"
sFile = "MyFile.MDB"
sPW = "PassWord"

compactDB(sPath & sFile, sPath & "Temp_" & sFile)

Public Function compactDB(ByVal SOUR_path As String, _
                          ByVal DEST_path As String) As Boolean

compactDB = True  ' Assume success

'  We pass the fully qualified file names including path

'  This requires a VB reference to:
'      Microsoft Jet and Replication Objects Library
On Error GoTo Err_compact
Dim JRO As New JRO.JetEngine

' Source and Destination connection path
Dim DB_sour As String, DB_dest As String

'  Check for keypress

'  Connection string
DB_sour = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& SOUR_path & " ;Jet OLEDB:Database Password=" & sPW
DB_dest = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& DEST_path & " ;Jet OLEDB:Database Password=" & sPW _
& " ;Jet OLEDB:Engine Type=5"

'  Main compact method
JRO.CompactDatabase DB_sour, DB_dest

'  We created a temp file.
'  Delete the original and rename the temp.
Call RenameFile(SOUR_path, DEST_path)

Exit Function

    compactDB = False
    HasCompactErrors = True
    'write error to pilot log
errorreporting ("Problem . . .Compact Error ")

End Function

Sub RenameFile(DeleteThisOne, RenameThisOne)
    Dim fs, f, s, notfound
    notfound = False
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(DeleteThisOne)
    Name RenameThisOne As DeleteThisOne
End Sub
Hi tubst,

This is a copy&paste from your profile:
Knowledgebase Stats:    
Questions Asked 50
Last 10 Grades Given A B C B B B A A B C  
Question Grading Record 39 Answers Graded / 40 Answers Received

Please resolve your forgotten qestions.

If you need any assistance with that, please post a 0 (zero) points question at the community support topic area, adding the link to the Qestion you need help with, and a short description of the problem, and a moderator will help you in no time.


Regarding your question:

I suggest thet you create a new table to hold just one parameter (True/False).

The usual value of the field will be True. On the Application load event (or the OnLoad event of your switchboard), add a routine like the following pseodo-code:

If TheFieldsValueInTable = True then
   Activate Application
   TheFieldsValueInTable = True
   Quit Application
End If

Before you start the Compact (on exit) you should do:
TheFieldsValueInTable = False


Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
You cannot compact the database that is currently open.  Instead, you must call another MDB with the name of the database to be compacted in some way (ie. an INI file) and have it do the actual compaction.  There is code available to do this from several sources, which I can list if you want to go this route.

Also, in A2000 and up, there is now an option to compact on close.  After the last person exits, the database is acutomatically compacted.

tubstAuthor Commented:
thanks, genehead!
All Courses

From novice to tech pro — start learning today.