MDB File Size Increased drastically

[harishj]

I ship access database with my application..

It usually ships with 14 MB Size

At one client site the size of database (mdb) file was drastically increased by nearly 200% i.e. 280 MB after some days of usage..

I am not able to understand why this has happened even when there is no much data available in the database..

Any help is greatly appreciated..

Kindly reply with the solution..

Thanks in advance

Harish.

[MAK]

Click Tools-Database utilities -compact and repair database will shrink the database.

you can use /compact option during startup.

It is not the data, it is the log file/temporary space or unwanted space (when you delete objects and data) ms-access uses has been increased in the MDB file.

from ms-access help

If you delete data or objects in an Access database, or if you delete objects in an Access project, the Access database or Access project can become fragmented and use disk space inefficiently. Compacting the Access database or Access project makes a copy of the file and rearranges how the file is stored on your disk.

[harishj]

Thanks for the reply..

I tried out all things
repairing,compacting and even re-compiling

the size decreased to 128 MB still a difference of about 110 MB

pls help me if anything else can be done..

thanks and regards,

Harish.

[Sand75]

... Choose to compact your DB each time you close it (be careful to have enough space disk).

To do that, open your database, then go to Tools/option, you will have the option in the "general" page.

[harishj]

which option you r talking about..

In general page there is option of
1. Print Margins
2. Default Database Folder
3. New Database Sort Order
4. Provide feedback with Sound
there is no option for compact database at close

I am using Ms-Office 97

pls tell me exactly where i have to look into for..


thanks & Regards,
Harish.

[Sand75]

The option I told you is available only on access 2000.
But you can also use :
- In VBA, the "compactdatabase" method. It's a little bit difficult because this method can't be use in the DB which has to be compacted. Refer to the Access Help for more details.
- You can create a .bat file which will contain one line :
"C:\Program files\...\MsAccess.exe" "YourDB.mdb" /compact
After you can launch this .bat file by batch every day and it will automatically compact your DB. The MsAccess DB is closed after the compact command.
I hope it helps you...

[xordevoreaux]

Reasons your database can bloat (compacting only solves some of this -- decompiling / recompiling is necessary for the rest, if you code / use macros).


1. MS Access is file-based, not server transaction based, so you're always writing and rewriting to the hard drive for a variable space. To get around this, switch to MS Access ADP files using either MDSE, which you can install from the MS Office Professional CD by browsing to it on the CD (not part of the installation wizard), or, hook the database up to a server, such as SqlServer. You'll have to build a new MS Access document of type ADP (as opposed to MDB). Doing so puts you in a different developmental regime, however, than you're used to, so read about this before doing it.

2. Compiling. Using macros plus the "compile in background" option is no different than compiling your MS Access project by having coded in Access Basic, Visual Basic for Access, or Visual Basic using the VB Editor that comes with MS Access.

Whatever changes you made last time remain as compiled pseudocode, so you are pancaking one change on top of another, even though you only are playing with the lastest version of your code. I detailed in a forum submission late last year or early this year how to decompile/recompile so that you're only using the most current pseudocode compilation. I'll try to hunt that down (not right this minute) to resubmit it.

3. Queries, especially large queries, take up space when they're run which is never reclaimed until you compact. You can make your queries more efficient, but you'll never get away from this completely.

4. Locktypes, cursortypes, and cursorlocations on ADODB, depending on how you set them up, can take up a lot of space if you choose combinations that are really data intensive. These can be marshalled (configured) in such a way to return only what's necessary. There is a knowledge base article on the MDSN library at microsoft.com detailing how ADODB causes a lot of bloat, and recommends to use DAO, but this is a cop-out; what you do is use ADODB well and you'll get around this, and DAO does not eliminate bloat, either.


5. DAO functions.

6. Object creation -- tables, forms, controls, reports -- all take up space. If you create a form and delete it later, the space that the form is not reclaimed until you compact.

7. Cute pictures. These always take up space, and MS Access does not store them efficiently. A 20K JPEG can wind up like an 800K or 1MB bitmap format once stored in Access, and there's nothing you can do about that in MS Access 97. You can put the image on a form and use subform references of the image where ever you want it, but you still don't get around the inefficient storage format.


8. OLE Objects. If you have an OLE field and decide to insert, say, a spreadsheet in that field, you take the entire Excel Workbook with it, not just that sheet. Be careful how to use OLE objects.

9. Table properties with the subtable set to [auto]. Set this property, for all tables, to [none]. Depending on how many tables you have, performance can also perceptibly improve.


You can also get the Jet Compact utility from Microsoft.com for databases that are corrupted.

[mameluk]

That was some interesting stuff here on forums.databasejournal.com Thanks for posting it.