Compress DB

Topic

New Reply

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.

The difference between Genius and Stupidity:
A Genius knows their limits.
- Albert Einstein

Reply | Quote

Replies

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?

Reply | Quote

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.

The difference between Genius and Stupidity:
A Genius knows their limits.
- Albert Einstein

Reply | Quote

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.

Reply | Quote

Thanks for your quick response – 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* process up and running.

The difference between Genius and Stupidity:
A Genius knows their limits.
- Albert Einstein

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

Ed: There’s very good information available from Dev Ashish at The Access Web dealing with just this situation. Hope it helps.

Reply | Quote