Compact an Access Database from DTS

Question

Compact an Access Database from DTS

Peter Williams

SSC Veteran

Points: 217
More actions
July 20, 2005 at 5:48 am

#167594

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

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

Henrik Staun Poulsen-237657 Old Hand Points: 346 More actions · Answer 2

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

Peter Williams SSC Veteran Points: 217 More actions · Answer 3

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.

Henrik Staun Poulsen-237657 Old Hand Points: 346 More actions · Answer 4

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

Habib Zmerli Mr or Mrs. 500 Points: 559 More actions · Answer 5

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)

Ian Paskin SSC Journeyman Points: 87 More actions · Answer 6

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

SQL Terminator Old Hand Points: 340 More actions · Answer 7

In Process Tab of Execute Process Task Editor:

Executable:

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

Arguments:

/compact/repair "S:\Filename.mdb"