[AccessD] Compact by code

I still use the function today. 

The variable strOldDb is the fully qualified directory and name of the
database to compact. The variable strNewDB is the fully qualified directory
and temporary db name to compact the strOldDb to. Both directories should be
the same so the Name function will work properly.


Public Function CompactDB_JVR(strOldDb As String, strNewDB as String) As
Boolean
'---------------------------------------------------------------------------
------------
' Procedure : CompactDB_JVR
' DateTime  : 7/14/2004 08:43
' Author    : John Ruff
' Purpose   : Compact selected databases
' Note      : Set Reference to Microsoft Scripting Runtime Library
'                 and Microsoft Jet And Replication Objects X.X Library
'---------------------------------------------------------------------------
'
    Dim fso As New FileSystemObject
    Dim strNewDB As String
    
    On Error GoTo CompactDB_JVR_ERR
    
    ' If the strNewDB exists, delete it;
    ' otherwise an error will occur trying to compact the database
    If fso.FileExists(strNewDB) Then
        fso.DeleteFile (strNewDB)
    End If
    
    fOK = PerformCompact_JVR(StrOldDb, strNewDB)
    ' If the compact is successful, delete the old database and rename the
new database
    If fOK = True Then
        fso.DeleteFile (StrOldDb)
        Name strNewDB As StrOldDb
    End If
   
    CompactDB_JVR = True
    
CompactDB_JVR_EXIT:

    On Error Resume Next
    Exit Function

CompactDB_JVR_ERR:

    CompactDB_JVR = False
    MsgBox "Error " & Err.Number & _
                " (" & Err.Description & ") in procedure " & _
                "CompactDB_JVR of Module Module1", _
                Title:="CompactDB_JVR Error"
    Resume CompactDB_JVR_EXIT
    
End Function

John V. Ruff - The Eternal Optimist J
 
"Commit to the Lord whatever you do,
                and your plans will succeed." Proverbs 16:3
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Hindman
Sent: Monday, November 21, 2005 1:10 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Compact by code

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='BackUpManag
er.mdb'

William

----- Original Message ----- 
From: "John Colby" <jwcolby at ColbyConsulting.com>
To: "'Access Developers discussion and problem solving'" 
<accessd at databaseadvisors.com>
Sent: Monday, November 21, 2005 3:02 PM
Subject: [AccessD] Compact by code


> Does anyone have code to compact an external database?  I am working on a
> transformation / migration system.  Append operations go to an external
> database using the IN 'SomeDatabase' clause in SQL.  The results are a 
> huge
> database bloat, not sure if it has anything to do with the IN clause or 
> not,
> but huge none the less.
>
> So I need to do a compact on that backup database from code.
>
> I see two methods in help - using DAO DBEngine, the other uses the 
> JetEngine
> object.
>
> I'm just looking for any pointers from anyone who does this already.
>
> John W. Colby
> www.ColbyConsulting.com
>
> Contribute your unused CPU cycles to a good cause:
> http://folding.stanford.edu/
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com