Compact and repair - 2003 vs 2007

[MrDeej]

Hello!

In Access 2003 this function worked fine, and compressed my databases nicely. But in Access 2007 with .accdb databases it just keeps growing and growing for eternity, or at least until i import all object to a new .accdb file.

Anybody have any idea on this?

We use Office 2007 SP1 without any other updates

[MrDeej]

Ok, i found the solution myself.

I need to copy the .accdb from the network disk to a local disk. Then compress and repair, then copy back.

Then the compressing works. Stupid bug by M$, anybody now of a workaround to this i will be glad know of it :=)

[ChipR]

Before I moved to 2007 I had some problems with a picture that just kept increasing the size of the db. I was pretty sure they fixed that though.

What kind of size are you talking about? For some perspective, my 50 queries, 100 forms, and 20 reports are around 170MB even though I have it on Compact on Close. No pictures though.

The Office Button -> Manage -> Back Up Database actually creates a <5MB file which it seems I can use just like the original, but it slowly grows as I use the forms, etc.

[ChipR]

You are absolutely right. Mine was on a network drive and wasn't actually compacting either, though it had a progress bar as if it were. I copied it to the local disk and it did work.

[MrDeej]

I have a database with
13 local tables
43 linked tables
32 forms
17 reports
10 modules

Before compressing: 55mb
After compressing: 6mb

[ChipR]

That doesn't seems out of control. Is it really continuously growing or is it stable at 55MB?

[MrDeej]

The growth from around 6 to 55 happend in 3 days :=)

But in my code i use a lot of creating temptables for manipulating and showing data in the form i want the data to be shown. This i know make the database grow intensly, and thats why it is superannoying that M$ havent made a fix for this bug.

Sometimes querys dosent do it for my and i have to make temptables.

[MrDeej]

This still is a problem, anyone know of a solution to this?

[ChipR]

Try working with recordsets instead of temporary tables.

[MrDeej]

@ChipR
I have done that. I now use listbox.addnew (or something, cant remeber now) instead of temporary tables. And all the previous local tables are now SQL

Still it has grown from 17mb til 177mb in 4 days and i have to move it to local drive to be able to compact it. I have several places a picture which loads a 2-3mb jpeg file from our product catalog (800 products). I also have some extremely complicated queries, some of them are so complicated that ADO fails when reading them and i have to use DAO

So the issue with compressing on network drive is still a big pain in the arse:)

[ChipR]

DId you try using it for a while with no pictures? I had major issues with a logo I had on my main menu. The size of the db just kept increasing until I gave up and removed the picture. At least it might help you to narrow down the problem.

[MrDeej]

No i havent tried that. The pictures are part of our quality control and are used constantly by atleast 10 employes maybe 20 times each pr day.

But i rather should find a way to make the compress thing work rather than removing functionality. I know that the growing issue got bigger the more pictures i programmed it to pick up so it is partly because of that, but it grew anyways. I would love to could use the "compress on exit" feature, but it does not work when the .accdb is on a network drive.