Size Discrepancy

[BenSteckler]

Hi Everyone,

Quick question. I have a database that runs a Macro that runs a couple of Make Table Querys. When it finishes, I run a few Delete Querys to empty out the tables.

But the problem comes in when I start the database, it starts with a size of 9.1 MB. But when I finish, it has grown to 144 MB. I looked thorough all the tables and they all are empty.

Can anyone explain where this huge size hog is coming from?

 

[Cosmos75]

Compact you database

Are you compacting your database?

EDIT: When you delete entries, Access doesn't actually delete the data, just hides it from you (not that you can do anything with it). The only way to get rid of the space that Access is reserving for the "deleted" data is to compact your database, it should be under tools.

If you are using Access 2000, you can have Access compact the database automatically everytime you close the database.

 

[cogent1]

Compact your database. Size bloat is an inherent feature of Access.

 

[BenSteckler]

No, I have never hear of compacting a database. Can you breifly explain?

I was thinking that maybe for some reason, when running a delete query, Access does not actually delete the data but stores it in a 'recycle bin' of some sort that is hidden in the database.

Thanks for the quick reply.
BDS

 

[Cosmos75]

In Access 2000, go to Tools, then Database utilities and choose Compact database, in Access 97 I think it says Compact and Repair Database.

EDIT: In Access 2000 , go to Tools, Options and click the General Tab and check Compact on Close. Access will now automatically compact the database when you close it.

 

[BenSteckler]

Thank You!

Thank Cosmos75 and Cogent1,

I have dropped the size from 144 MB to 800KB.

I appreciate the help,
BDS

 

[Cosmos75]

Glad to be of help!

You're very welcome! I didn't know about that either till I started creating Access databases a few months ago! Am glad to share what I've learned since then! Am still learning!