shrink / compact

[joe789]

Hey folks,

I notice that microsoft access database files can get gigantic very quickly (and reach the max 2 gig file size thereby rendering the database useless at times), and that when the shrink/compact/fix command is used the database becomes a fraction of the original size. I am wondering if there is any program that can just shrink all databases in a folder ... I have dozens and dozens of databases and shrinking each one seperately once in a while takes forever. Also, is there any way to make mdb files more reasonable in size without having to always use the shrink/compact/fix command? And my last question is, is there anyway to get beyond the 2 gig file restriction ... I notice that if I run many queries, if the 2 gig file size is reached the database is not capable of doing much until I shrink/compact/fix.

Thanks,

Joe

 

[georgedwilkinson]

Use SQL Server, MySQL, or Oracle. You can unify your databases and split them up into several database files if needed without compromising by having your data in disparate places. You can keep your Access front ends.

 

[Flea]

There is an option to compact automatically when the database is closed.
Tools/Options/General tab - Compact on Close.

It will not compact the database if someone else has it open. Also, if you have a split database then this would not compact any attached databases.

 

[wazz]

someone (kenhigg?) talked about a way of compacting (by code) only when the db reaches a certain size; he might have even posted a sample db (search). severe bloat can be caused by storing images - store images outside the db if this is the case.

 

[boblarson]

And my last question is, is there anyway to get beyond the 2 gig file restriction

You can do several things -

1. You can use separate mdb files for a single table and link to each from the frontend.

2. You could upgrade to SQL Server which will let you have as much data as your server will hold.

Also, if your database is growing very quickly due to queries, I'm wondering if you are using make table queries frequently as those can do exactly that, bloat the database.

 

[RuralGuy]

Wazz,
ghudson posted the "Compact on Close if the file is too big" code here.