[AccessD] Compact by code

I use the following code with no problems.  I used to rename the original db
to something else afterwards as a back-up, but for what I use it for, there
was not really a need to.

Bobby

Public Sub CompactDB(ByRef strDB As String)
Dim strNewDB As String

   On Error GoTo Proc_Err

   'temp file name   
   strNewDB = GetFilePath(strDB) & "xyzTemp.mdb"
   
   On Error Resume Next
   Kill strNewDB			'delete temp file if it exists
   
   On Error GoTo Proc_Err
   'compact the database
   DBEngine.CompactDatabase strDB, strNewDB, , , ";pwd=" & SECUREPW
      
   'delete the original db
   Kill strDB
   'rename temp db to original name
   Name strNewDB As strDB
   
Proc_Exit:
   Exit Sub
      
Proc_Err:
   MsgBox "The following error occured: " & Err.Number & ":  " &
Err.Description
   Resume Proc_Exit
   Resume

End Sub

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Colby
Sent: Monday, November 21, 2005 3:03 PM
To: 'Access Developers discussion and problem solving'
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