[AccessD] Compact by code

LOL, why's that?  All done in-house.

Bobby

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


Gosh, that code sure looks familiar! ;->

Charlotte Foust


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


Sorry, GetFilePath() returns the path of a given file name string.

Bobby

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

-- 
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
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com