Database size!!!

[davea300]

I have a .mdb file (access 2003) which has ballooned in size to 1.2GB and I'm not sure why. I tried deleting some older tables (which were copies and had about 38,000 rows each) but it hasn't made a dent. Is there a way to show all the database elements tables, forms, macros, modules etc and list their sizes so I can see what has caused the size to increase?

There are 3 tables linked via ODBC which have tens of thousands of rows but as these are linked I wouldn't have thought they would have increased the size at all?

 

[pr2-eugin]

Try a Compact & Repair..

When you delete tables/objects/rows in table the allocated space for those objects are not reclaimed (Garbage collection) unlike some sophisticated programs.. You have to do it, so a constant Compact and Repair will make sure your DB is always reclaiming any garbage space and also increases the efficiency of the working...

 

[davea300]

already have, went from 1.25GB to 1.15GB

 

[jdraw]

That really isn't the change in size I would expect. 1.15GB seems a quite large database.
Not excessive, but if you have deleted " a lot", I'd expect a bigger reduction.

 

[Simon_MT]

Do you use any images or temp files?

Simon

 

[davea300]

There are some image files associated with some records but these haven't increased in number for over a year now. I take it there is no built in method for me to analyse each element of the database so I can figure out whats causing the size to increase?

 

[pr2-eugin]

So what is the new change that you made in the last few weeks? Backtrack your steps.. Any suspicious coding?

 

[davea300]

The only thing I've added recently is 3 linked tables to an oracle database. nothing else apart from a few minor form corrections.

 

[gemma-the-husky]

would it be possible to remove the images, to see if that makes a difference.

images seem to be the main reason for size increases. I know my databases of this size would have at least a million records.

the only other thing is a lot of very large memo fields. numbers only occupy a handful of bytes, but long strings could use a lot of space.

 

[pr2-eugin]

So if you delete the Linked tables, perform a Compact and Repair and then Relink.. See if that helps.. I have not used ODBC in my application, so I am not sure how that would affect this enormous size growth..

 

[Christian1977]

Hi,

I'll try these way:

1) Import all DB in microsoft Office 2010
2) Import the 3 tables linked via ODBC in a new DB and then link these three tables on the main DB

Really interesting the question about a way to show all the database elements tables, forms, macros, modules etc and list their sizes.

Let me know,

Christian