Compacting a growing 2000 mdb

[neil_turner]

Hi All,

I hope someone has the answer for me here as I have been asked to save some space on our server which has about 12 large mdb files upon them all of which are in Access 2000. I went through the first one and changed all the feild sizes to suit the data which hardly made any difference (I then vaguly remember reading that this is done automatically in access 2000. Is this right?). I then set about compacting and repairing.

Due to the size of these files, most well over a GB compacting takes ages and dosen't reduce the size. A lot of the time it dosen't even complete the compaction as it runs out of memory (sigh)!

Most of these databases are the same style a macro runs creates loads of temporary tables then prints a report updates a table of data to the latest values and then removes all the temporary tables. The size of this database should not grow 200 Mb every time the macro is run but it does.

I am aware of of using the decompile command line option but that makes no difference. It still dosen't compact back down to its original size which in theory of what its doing it should.

I am leaving this down to an assumption that access can not handle large databases. could it be possible due to the size of the database that the compaction routine is causing more rollback information than its removing?

Many Thanks
Neil

 

[Fizzio]

Try reading this http://www.databasecreations.com/largedb.htm

or look to upsizing to SQL server (A daunting thought I have yet to explore!)

 

[neil_turner]

I've tried everything in that article already to no effect, for some reason Access 2000 really dosen't want to play ball.

I know SQL server would be a great option unfortunatly the work is there as a proof of concept which will only be used occasionly where the full solution will be much larger and complete on an Oracle database. Looks like its a no to be able to save space.

Cheers for your help

Neil

 

[Fizzio]

Sounds like you have considered everything but.... do you have any OLE objects stored in the Db?? They can cause massive bloat. If so, consider using file links rather than storing the actual object (but I'm sure you have already considered this)

 

[neil_turner]

Thanks for your help, but no there are no OLE columns within the tables, infact apart from the tables and queries theres nothing else in them oh and a macro to run them in order. Unfortunatly its just such a size due to the amount of rows of data and then temp tables being created from querys of these tables.

It would just be nice to find a reason why Access 2000 wont recover the used space properly considering its running on a win2K server you would think memory managment would be queit good, I assume its just one of those things.

Cheers
Neil

 

[Fizzio]

Do you have a FE/BE setup??. Just a thought - if you are creating temp tables from queries in your backend database (data storage only) you will create unbelievable bloat. A better way would be to split the Db into frontend/backend and create the temp tables on the frontend database (houses everything else - only links to tables) as the bloat will (possibly) be less and you can refresh this database at certain times eg every week to restore the original, non bloated frontend.
Therefore the data storage Db remains unscathed and bloat will be due to added data only, not the creation of new objects.
When the Db is split, you can apply all the tips from the above link to each separate Db.
I may be teaching granny to suck eggs but might as well throw in this one as well

Kristin

 

[Freddy]

I'm not sure if this will help, so I'll throw it out for others to answer:

Is is worth moving the temporary tables to a separate back-end db? It just seems more effecient to put the tables that'll benefit most from compacting onto one location. The separate db's can then be compacted on different schedules.

 

[ElsVanMiert]

The problem is definitely the temporary tables. Once they are deleted they still require space inside the MDB file. Only compacting (releases up to 80%) or decompiling (releases 100%) will help.
I strongly suggest that you de-locate your temporary tables either in a linked MDB or - MUCH better - use a CSV file.