Database keeps doubling in size!! Help!

[meanster99]

Can someone please help me! I have created a db in Access 2003 and the overall file size keeps doubling periodically. Before I noticed this phenomenon the DB was at 65Mb (could have been happening earlier). It then doubled to around 120Mb, then 241Mb and finally it was at 480Mb!! I had a back-up of the 65Mb version so I just copied the new tables/queries/forms from the latest db (480Mb) into the backup (65Mb) and it was OK for a while until it started doubling again. I tried to determine what was making it double by going through all the available actions etc but it seemed to be doing randomly! I have read on this forum something similar but the answer to that thread didnt help me.

I am a bit of a novice with DB's and i haven't split the db into Front-end and Back-end, but its quite small (around 20 tables but only 3 are of any real size, 25 queries, 20 reports, 8 modules (all performing mail-merge programmatically) and around 10 macros)

Does anyone have any idea why this is happening and how it can be prevented? I have been working on the db for months, with gradual additions to it as and when needed and so I would rather not have to start from scratch! (I am worried that my lack of db knowledge when i started may have meant i used bad practices - which is starting to manifest itself now!)

[cweiss]

Try going to Tools->Database Utilities->Compact and Repair

I think when you delete tables and such from Access, they're still somewhere in the background, so you have to compact the db to get rid of them permanently.

[meanster99]

thanks cweiss

this has reduced the db to 14.5Mb but will this eliminate the problem for good? (or will I have to wait to find out!!)

Thanks for your help though. If anyone else know anymore about this db size explosion please help!

[cweiss]

If you go to Tools-Options->General, there's an option to Compact on Close. If you check it, then you shouldn't have to worry about this problem in the future

To state the obvious, this will prevent your database from becoming bloated by compacting every time you close it :).

[meanster99]

If you go to Tools-Options->General, there's an option to Compact on Close. If you check it, then you shouldn't have to worry about this problem in the future

To state the obvious, this will prevent your database from becoming bloated by compacting every time you close it :).

Thanks again. This will certainly work for the time being - I am putting the db onto a webserver (not my own) in the near future for users to access online - do you think this will cause any further size explosion problems (will your fix still work?)

[cweiss]

Hmm, I've never used access as a backend in web development so I can't say for sure, if it always compacts on close I would think you'll be okay.

From what I can tell, this is caused because when some object (like a table), or data in your database, is deleted, it's not really deleted, it still resides somewhere in the database. To completely eliminate it you have to compact and repair.

I suppose as long as your users aren't deleting tables or data often, you shouldn't have to worry about it too much.

Worst case, you'll need to compact and repair it periodically.

[wlc04]

Access will always increase in size when it's used. By checking the compact on close option it will keep the size in check for you.