Access Database Compression

[Jassy]

We have a real time process and i have to log all the data periodically into an Access database using VB6 as front end for reporting purposes.

The problem is that the size of the database increases drastically so i purge the data after 30 days (Delete the records older than 30 days).

But i observed that the database size does not reduce even after purging the records.

What do i need to reduce the size of the database ?

Datamanager allows me to compress the database to a new file. How Can i comperss the database from VB ?

I also need to send the database file after zipping & compressing it to the floppy drive how can i do it from VB6 ?

Jaspal Muker

[JHausmann]

To compact your database, try the following:

DBEngine.CompactDatabase olddb,newdb

To zip the file, I'm not sure.

[Clunietp]

When you delete records, they are only MARKED for deletion, which means that those marked records remain in the database until you compact the database. You can do this programmatically via code (DAO with DBENGINE as described above or ADO with MS Jet Replication objects) but you need to have exclusive access to the database at the time of compacting

HTH

Tom

[Clunietp]

I wish you would stop being a ***** about my answers hausman. I'm just trying to help here and you gotta be on my ass and check every last technical accuracy of every one of my statements and be some kind of hero...

regardless if the record is actually erased or marked for erasure makes no difference -- the space is there and can't be used until the db is compacted

If only we all could be as "smart" (anal) as you the world would be a better place

[JHausmann]

Um, as far as deletions go, this is not true for Access (Foxpro, yes).

From the VB documentation for CompactDatabase:

"As you change data in a database, the database file can become fragemented and use more disk space than necessary"


You can fragment your database by doing certain types of updates (one's that change a key field or increase the size of the data stored in a text field), as well as deletions. Access deletes (and changes to certain records) by removing the old data but it doesn't reclaim the previously used space until a compact is performed.

[JHausmann]

A bad answer, Tom, is worse than no answer.

[JHausmann]

Yes, it does make a difference. If a record is physically deleted, it is gone and it cannot be restored/recovered. By stating the record is only flagged, you've implied the record can be recovered by "un-flagging" it, somehow.

Your statement "When you delete records, they are only MARKED for deletion, which means that those marked records remain in the database until you compact the database" is not a "minor" innacuracy, when applied to Access.

[Clunietp]

You are correct. If I did somehow imply that the records could be unmarked for deletion, that would make a big difference. Thank you for raising this point, I didn't think of that aspect, I just thought you were trying to show the world how smart you are. No harm, no foul. Sorry!

Take Care

Tom

[Clunietp]

does it really matter if the record is marked for deletion or the space is just left there unused? either way it is unusuable space. My answer was not the most accurately worded one, but it did answer the question with enough accuracy in a timely fashion without too much detail. I admit I do not usually research the answers I give and apologize for any MINOR inaccuracies (such as this one)