Results 1 to 10 of 10

Thread: Compress DB

  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Phoenix, Arizona, USA
    Posts
    265
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Compress DB

    Is there a simple straight forward way to automatically compress a database after a bunch of data has been imported using vb? Using Access 97 ADODB.

    I know *simple* and *straight forward* are not really a part of Microsoft's vocabulary!

    TIA.
    Ed
    "Somebody left the cork out of my lunch." - W. C. Fields

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Compress DB

    Are you using VB or VBA? Is your front end in Access or VB? Are you trying to compact the current database or a database that isn't currently open?
    Charlotte

  3. #3
    coreman
    Guest

    Re: Compress DB

    Look for a utility called "JetComp" on Microsoft's site.

    Use this application to do it. How? Write a batch file that calls it and stick it in your scheduler app or the Startup folder in your profile (so it runs when you login/start Windows).

    JetComp can compress/repair Access MDBs that Access cannot, since it doesn't open the file the way Access does. Plus, it doesn't require the overhead of Access to do it.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Phoenix, Arizona, USA
    Posts
    265
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Compress DB

    I guess that is where my quandry lies - I don't know whether to use VB or VBA. Front end is in VB as I'm loading data from a mainframe application (data is FTP'd to server, then VB script is doing the load of the DB). The scenario is as follows - I load a lot of data (more than 35K records - in one case almost 300K records), and I know that there is a lot of overhead and bloat generated when I load the records. I need to do this *load* at least six different times (with data from six different sources), and would like to compress the DB between loads. Doing the compress manually (between loads) buys me at least 75% space reduction (sometimes more - usually depends on how many records get loaded). I am running this on an NT4 SR6a server using Access 97 and need to keep the total amount of space used to a minimum. The database would be closed at the time of compression as it would be after the *load* of the data.
    Ed
    "Somebody left the cork out of my lunch." - W. C. Fields

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Compress DB

    I agree. JetComp is the simplest way to do it, and it will work from VB or a batch file. It used to be included in the Jet service packs but is now a separate download. If the backend is in Access 97, download the last service pack for Jet 3.5 and JetComp will be included. It is not included in the latest service packs for Jet 4.
    Charlotte

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Phoenix, Arizona, USA
    Posts
    265
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Compress DB

    Thanks for your quick response <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> - now it's up to me to go off and get it working! We have Jet 4 loaded, so I just hope that I can mesh everything together and get an *automatic* <img src=/S/headthrob.gif border=0 alt=headthrob width=15 height=15> process up and running.
    Ed
    "Somebody left the cork out of my lunch." - W. C. Fields

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Compress DB

    Be VERY careful using the Jet 4 version of JetComp with Access 97. That version of JetComp defaults to Jet 4 format, although you can select 3.x from the interface (and I assume from code). If you compact an Access 97 database to a Jet 4 format, you will have garbage that isn't either Access 97 or 2000.
    Charlotte

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Compress DB

    I downloaded JetComp to see if I could use it in an automated process. I read the docs, but found no answer to this: Can the DB to be compacted (source & destination) be passed on to JetComp as command line arguments?

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Compress DB

    I've never tried it, but it should be possible. You'll probably have to fully reference the JetComp.exe path to make it work though.

    Just be aware that JetComp will NOT compact to an existing filename, so you'll have to either rename the source database before you compact it or delete the source and rename the destination file afterwards.
    Charlotte

  10. #10
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compress DB

    Ed: There's very good information available from Dev Ashish at <A target="_blank" HREF=http://www.mvps.org/access/general/gen0013.htm>The Access Web</A> dealing with just this situation. Hope it helps.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •