Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Compact an Access Database from DTS Expand / Collapse
Author
Message
Posted Wednesday, July 20, 2005 5:48 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 13, 2006 7:00 AM
Points: 95, Visits: 1

Does anyone have any ideas on how to Compact & repair an Access datbase within a DTS package??

I basically have a 'delete from table' statement.  I then export data from SQL Server into an Access database each night which contains updated results.  The Access database is growing beyond believe.  On last look, the mdb file was 300Mb.  I compacted and repaired this database from within Access itself and the file reduced to 13mb!! 

I have seen a couple of articles which point towards using this executable (http://support.microsoft.com/default.aspx?scid=kb;en-us;273956) however if possible I would prefer to use an ActiveX script at the end of processing, just to tidy things up and to avoid firing up server side applications in the background.

Any help would be much much much appreciated!

Thanks

Pete

Post #202705
Posted Wednesday, July 20, 2005 6:24 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 22, 2011 5:35 AM
Points: 1,758, Visits: 9

1.  I would ensure that EVERY UPDATE, INSERT, DELETE query inside the Access database has "Use Transaction" property = FALSE/NO.  This will DRAMATICALLY INCREASE the size of your DB if TRUE/YES.

As far as doing this via DTS I would NOT recommend it.  You don't know if someone has left the application open in which case you will NOT be able to and could possibly corrupt it.

Instead, how about adding a button or an ON database CLOSE event to COMPACT/REPAIR ?





Good Hunting!

AJ Ahrens


webmaster@kritter.net
Post #202713
Posted Thursday, July 21, 2005 2:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, February 25, 2013 6:45 AM
Points: 70, Visits: 15

Pete,

I've tried getting DTS to delete records from an Access database to no avail. I ended up running a "copy" command to get an new empty copy of my Access database. BTW: The copy does not really support long filenames.

HIH

Henrik Staun Poulsen

Stovi Software

 

Post #203006
Posted Thursday, July 21, 2005 2:59 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 13, 2006 7:00 AM
Points: 95, Visits: 1

Thanks for your replies.  I think we're going to try a different approach rather than DTS.

Just to make my question a little clearer, I'll explain the scenario.

We have a SQL Server database which stores online internet activity for a range of clients.  This database is currently 24 GB.  We export individual client data to seperate Access databases (so in effect, each client has its own access database containing it's own activity for the current week).

At the end of the week, we empty the access database and export the current week's online data.  So to summarise, our SQL Server is the datawarehouse, the Access database just gets updated with the last 7 days worth of activity.

Even though we delete data from the Access database and put new data in there, the file size continues to grow.  Therefore, we need to compact the access files once they've been updated.

From what I gather, DTS isn't the best approach for this final step, so we're going to write a VB app and schedule it to run at certain intervals.

Thanks for everyones feed back.

Post #203009
Posted Thursday, July 21, 2005 3:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, February 25, 2013 6:45 AM
Points: 70, Visits: 15

Pete,

We're doing pretty much the same job as you are, and DTS runs just fine. If your only problem is that you cannot clear down the Access db, then it is easy to fix this, with my sugestion; as in

copy myEmptyDB.MDB ClientA2005_07_21.mdb

Best regards,

Henrik

 

Post #203021
Posted Thursday, July 21, 2005 7:41 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 14, 2008 5:14 AM
Points: 83, Visits: 3

Use the "Execute Process Task" with the following command :

"c:\Program Files\Microsoft Office\Office11\msaccess.exe /compact /repair"

PS. Parameters can be localted in the parameters field.

HABIB.

 




Kindest Regards,

Habib Zmerli (MVP)

Post #203096
Posted Thursday, July 21, 2005 9:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 5, 2010 10:29 AM
Points: 5, Visits: 3

Pete,

I compact an access db from a VB program each night so you may be able to use this to create an activeX DTS package

*******************************************

Dim jetDBEngine As DBEngine
Dim daoDB As DAO.Database
Dim fsoFS As Scripting.FileSystemObject
Dim strdb as string

strdb = "C:\mydb.mdb"

Set jetDBEngine = New DAO.DBEngine
   
    'Compact Database into _Compacted file.
    Set fsoFS = New Scripting.FileSystemObject
  jetDBEngine.CompactDatabase strdb, strdb & "_Compacted"
   
    'Then copy _Compacted file back on top of live file
    fsoFS.CopyFile strdb & "_Compacted", strdb
    fsoFS.DeleteFile strdb & "_Compacted"
   
    'Establish Connection to Database
    Set daoDB = jetDBEngine.OpenDatabase(strdb)

*********************************************

Ian

 

 

Post #203142
Posted Thursday, May 16, 2013 12:43 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 10:03 AM
Points: 307, Visits: 530

In Process Tab of Execute Process Task Editor:

Executable:
C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE

Arguments:
/compact/repair "S:\Filename.mdb"
Post #1453738
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse