468,545 Members | 1,853 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,545 developers. It's quick & easy.

Database keeps doubling in size!! Help!

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!)
May 1 '06 #1
6 1395
cweiss
36
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.
May 1 '06 #2
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!
May 1 '06 #3
cweiss
36
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 :).
May 1 '06 #4
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?)
May 1 '06 #5
cweiss
36
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.
May 1 '06 #6
wlc04
70
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.
May 3 '06 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by cover | last post: by
30 posts views Thread by Neil | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by UniDue | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.