Increasing Database size (1 Viewer)

[shafh]

Hi all,
I have a weird problem. Everytime I run a macro (mcrUpdateLists) my database file size increases by about 4 MB. Even after I close the database and clear the clipboard and reopen the larger size is still present. New records are not being added to the database so I'm not sure why the size keeps increasing.
The mcrUpdateLists macro consists of a TransferSpreadsheet to upload a table, Run 2 Queries, an Updatequery, 2 Append Queries, Delete uploaded Table. However if there are no new records then nothing is updated or appended.
Thanks,
Sam H

 

[Fizzio]

Have you got Compact on close selected? - this tidies things up a bit.
All that importing and Deleting will cause bloat.

 

[The_Doc_Man]

shafh, you are running into the same problem that many folks do. You assume that Access is good about cleaning itself up. But sadly, it ain't so.

When you delete a table, you do not reclaim the space until you do a compression operation. This is because of the way the internal pointers are used internally when you create that table from the spreadsheet and later delete it. The internal pointers (really, addresses relative to the start of the DB) get deleted but the damage is already done. There is no way once a block has been allocated for Access to know that nothing else was allocated behind it and that it is safe to reclaim.

A compression operation forces all the internal pointers to be reevaluated as it copies the data to the temp-file that will be renamed to the original db name when the compression is finished.

On a big DB, this ain't cheap! Which is why it also ain't automatic.

 

[shafh]

ok. I'll set the compact on close option on.
thanks a lot guys. i had no idea access could be so inefficient.

 

[Fizzio]

If you find the size spiralling out of control, there are a couple of things that you can try. In this order, this will help to grab back that space Access has Guzzled up.

1. Import everthing into a new Db.
2. Restart access using the undocumented /decompile option
you will need to use the command line msaccess /decompile then load your new Db.
3. Close access and restart in normal mode
4. Load your Db
5. Compile the Db
6. Compact the Db