# Compressing Microsoft Access Data Files **URL:** \ **Category:** Factual Questions\ **Created:** [May 23, 2002, 6:10pm UTC](https://boards.straightdope.com/t/compressing-microsoft-access-data-files/110520 "2002-05-23T18:10:12Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![Mr.Slant](https://avatars.discourse-cdn.com/v4/letter/m/c57346/32.png) [@Mr.Slant](https://boards.straightdope.com/u/Mr.Slant)\ **Post date:** [May 23, 2002, 6:10pm UTC](https://boards.straightdope.com/t/compressing-microsoft-access-data-files/110520/1 "2002-05-23T18:10:12Z")
Is there a quick way to compress a number of MS Access data files (MDB format) that are located in a number of directories and sub-directories? I have a ton of these files with 300-400 KB wasted due to blank space in them. Since I’m forbidden from fixing the application that makes them in the first place, doing this would save me a bunch of space on one of my servers. ---
**Author:** ![Jpeg\_Jones](https://avatars.discourse-cdn.com/v4/letter/j/4bbf92/32.png) [@Jpeg\_Jones](https://boards.straightdope.com/u/Jpeg_Jones)\ **Post date:** [May 23, 2002, 9:05pm UTC](https://boards.straightdope.com/t/compressing-microsoft-access-data-files/110520/2 "2002-05-23T21:05:01Z")
What do you mean “…forbidden from fixing the application that makes them in the first place…”? Access 200 and up has a feature to compact and repair a database. You would have to do them one at a time, but this should work very nicely. You can also store the MDBs on a compressed NT volume. ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [May 23, 2002, 9:13pm UTC](https://boards.straightdope.com/t/compressing-microsoft-access-data-files/110520/3 "2002-05-23T21:13:03Z")
There’s a great free utility called ‘compact a list’ available [here](http://www.peterssoftware.com/cal.htm) - it also routinely repairs before compacting. (some of his other stuff is pretty good too). ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [May 23, 2002, 9:17pm UTC](https://boards.straightdope.com/t/compressing-microsoft-access-data-files/110520/4 "2002-05-23T21:17:07Z")
> [@](#): > > \*Originally posted by Jpeg Jones \* > \*\*What do you mean “…forbidden from fixing the application that makes them in the first place…”? > > Access 200 and up has a feature to compact and repair a database. You would have to do them one at a time, but this should work very nicely. > > You can also store the MDBs on a compressed NT volume. \*\* In A97 (and I _thought_ also in A2K) You can’t perform the compact operation programmatically from within the application itself, because it requires the database to be closed (you can do it manually from the menu option and in fact you can see Access close the database, compact it, then reopen it, which is why the startup form launches itself after a compact or repair). ---
**Author:** ![DarrenS](https://avatars.discourse-cdn.com/v4/letter/d/977dab/32.png) [@DarrenS](https://boards.straightdope.com/u/DarrenS)\ **Post date:** [May 23, 2002, 11:23pm UTC](https://boards.straightdope.com/t/compressing-microsoft-access-data-files/110520/5 "2002-05-23T23:23:48Z")
There is a command line option for Access - /compact - so you could create a .BAT file that compacts each db in turn: \\msaccess /compact db1.mdb \\msaccess /compact db2.mdb etc. where you put the real path to your Access installation instead of \ You could also do this with a WSH script if you wanted to get fancier, or even a loop in the .BAT file (can’t remember how to loop over files or I’d give you a sample). ---
**Author:** ![Jpeg\_Jones](https://avatars.discourse-cdn.com/v4/letter/j/4bbf92/32.png) [@Jpeg\_Jones](https://boards.straightdope.com/u/Jpeg_Jones)\ **Post date:** [May 24, 2002, 12:16am UTC](https://boards.straightdope.com/t/compressing-microsoft-access-data-files/110520/6 "2002-05-24T00:16:20Z")
> [@](#): > > \*Originally posted by Mangetout \* > **In A97 (and I _thought_ also in A2K) You can’t perform the compact operation programmatically from within the application itself, because it requires the database to be closed.** Yes, this is correct. The MDB does get closed during the process of compaction. Is this what Jonathan Woodall meant by “…forbidden from fixing the application that makes them in the first place…”? He can’t close the MDB? I bet DarrenS’s .BAT file would need to close the database also. ---
**Author:** ![Neptune](https://avatars.discourse-cdn.com/v4/letter/n/b5a626/32.png) [@Neptune](https://boards.straightdope.com/u/Neptune)\ **Post date:** [May 24, 2002, 12:46am UTC](https://boards.straightdope.com/t/compressing-microsoft-access-data-files/110520/7 "2002-05-24T00:46:01Z")
Does compact mean the same as compress? The question is, where is this extra space coming from? Compacting a database reclaims space that’s used up by deleted records. But I don’t think that’s what the OP is talking about. I suspect that “due to blank space” means that, for example, the programmers made a 50-character field to hold a person’s last name, where most people’s names are much shorter, and is therefore storing lots of blank space. If you are using Windows 2000 or Windows XP, you can select a file or directory and set it’s properties to be compressed. A file with lots of “blank space” will use significantly less disk space, but the change will be invisible to the application. On older operating systems there are tools that can do the same sort of thing. ZipMagic for example fools the operatiing system into treating a .zip file just like a directory. The difference is invisible to the applications using the files. Using compressed files can be somewhat slower since every disk read and write requires that the operating system compress and decompress data. So you want to balance speed against disk space. Or is your wasted space being caused by deleted records, in which case compacting is the answer. Another solution to your problem might be just to buy a bigger hard drive. If you had a 120 GB hard drive, you wouldn’t be worrying so much about 300 K of wasted space. ---
**Author:** ![DarrenS](https://avatars.discourse-cdn.com/v4/letter/d/977dab/32.png) [@DarrenS](https://boards.straightdope.com/u/DarrenS)\ **Post date:** [May 24, 2002, 12:51am UTC](https://boards.straightdope.com/t/compressing-microsoft-access-data-files/110520/8 "2002-05-24T00:51:45Z")
When you run Access with the /compact command line switch, it briefly opens and closes the database to do the job - you don’t have to explicitly close it. From the OP: \>I have a ton of these files with 300-400 KB wasted due to blank space in them it _sounds_ like the excess blank space is due to Access’s standard habit of not doing garbage collection until you select Repair & Compact - when you delete and change a lot of records, an mdb file can get huge until you compact it. I’ve seen Access databases go from several 10s of megabytes down to 10s of _kilobytes_ after doing a Repair & Compact. ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [May 24, 2002, 9:22am UTC](https://boards.straightdope.com/t/compressing-microsoft-access-data-files/110520/9 "2002-05-24T09:22:37Z")
It’s not just space from deleted records that Access reclaims during compaction - among other things, it deletes temporary objects that are created when queries are run (I think this includes query execution plans and temporary recordsets) - simply compressing the mdb file (externally, say with Winzip) won’t necessarily result in the same amount of space being saved because as far as Winzip is concerned, the garbage that Access hasn’t got around to binning yet is just part of the file. ---
**Author:** ![Mr.Slant](https://avatars.discourse-cdn.com/v4/letter/m/c57346/32.png) [@Mr.Slant](https://boards.straightdope.com/u/Mr.Slant)\ **Post date:** [May 24, 2002, 12:15pm UTC](https://boards.straightdope.com/t/compressing-microsoft-access-data-files/110520/10 "2002-05-24T12:15:31Z")
> [@](#): > > \*Originally posted by DarrenS \* > \*\*When you run Access with the /compact command line switch, it briefly opens and closes the database to do the job - you don’t have to explicitly close it. > > From the OP: > > \>I have a ton of these files with 300-400 KB wasted due to blank space in them > > it _sounds_ like the excess blank space is due to Access’s standard habit of not doing garbage collection until you select Repair & Compact - when you delete and change a lot of records, an mdb file can get huge until you compact it. I’ve seen Access databases go from several 10s of megabytes down to 10s of _kilobytes_ after doing a Repair & Compact. \*\* You are 100% correct here. And by the way, we’re worrying about this space because this is on a network drive, and our IT department is stingy about handing it out… we’re gonna’ wind up making about 1000 of these files annually, and we’d rather have to ask for 1 GB than 2 GB. ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [May 24, 2002, 1:15pm UTC](https://boards.straightdope.com/t/compressing-microsoft-access-data-files/110520/11 "2002-05-24T13:15:54Z")
You’re going to make 1000 Access database files? - why (if I amy be so bold) would you not be able to modify the design and store all the data in a single MDB? ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [May 24, 2002, 1:17pm UTC](https://boards.straightdope.com/t/compressing-microsoft-access-data-files/110520/12 "2002-05-24T13:17:48Z")
May, not ‘amy’ sheesh. ---
**Author:** ![Neptune](https://avatars.discourse-cdn.com/v4/letter/n/b5a626/32.png) [@Neptune](https://boards.straightdope.com/u/Neptune)\ **Post date:** [May 24, 2002, 2:10pm UTC](https://boards.straightdope.com/t/compressing-microsoft-access-data-files/110520/13 "2002-05-24T14:10:56Z")
> [@](#): > > we’d rather have to ask for 1 GB than 2 GB Explain to your IT department that it would be cheaper for them to give you 100 GB than it is to pay people to worry about disk space.