Database bloat in shared A2007 DB

[Randy Shore]

I have a shared Access mdb that was originally written in A2000. The
database is large, around 40 tables, 40 forms (many with multiple
sub-forms), and 100s of reports. The database is not split, but runs off of
the LAN in the office with about 5 or 6 concurrent users. Recently the
office upgraded to A2007 but they are continuing to run in compatability
mode.

The problem is that with ordinary use, read/write operations through the
user forms only, the database quickly bloats from about 60Mb to over 200Mb.
There are no import/export actions, no make-table queries, nothing like that
in ordinary use. Compacting and Repairing the mdb from A2007 doesn't shrink
the mdb appreciably in size, but if I open the file with A2003 and compact
and repair it shrinks back down to 60Mb again.

The only wrinkle I can throw into the mix is that one of the users is using
Vista while the rest of the users are on XPpro. I tried to lock the Vista
user out of the db for a day to see if that affected the problem, and the
results are mixed. In the following 2 hours I didn't see the db grow much
in size, although it did grow from 60Mb to about 70Mb. In the next 2 hours,
when I wasn't watching to make sure no one accessed the db from the Vista
machine it grew to over 200Mb again.

Anyone have any ideas?

[lyle fairfield]

"Randy Shore" <vt******************@roadrunner.comwrote in
news:47***********************@roadrunner.com:

I have a shared Access mdb that was originally written in A2000. The
database is large, around 40 tables, 40 forms (many with multiple
sub-forms), and 100s of reports. The database is not split, but runs
off of the LAN in the office with about 5 or 6 concurrent users.
Recently the office upgraded to A2007 but they are continuing to run
in compatability mode.

The problem is that with ordinary use, read/write operations through
the user forms only, the database quickly bloats from about 60Mb to
over 200Mb. There are no import/export actions, no make-table queries,
nothing like that in ordinary use. Compacting and Repairing the mdb
from A2007 doesn't shrink the mdb appreciably in size, but if I open
the file with A2003 and compact and repair it shrinks back down to
60Mb again.

The only wrinkle I can throw into the mix is that one of the users is
using Vista while the rest of the users are on XPpro. I tried to lock
the Vista user out of the db for a day to see if that affected the
problem, and the results are mixed. In the following 2 hours I didn't
see the db grow much in size, although it did grow from 60Mb to about
70Mb. In the next 2 hours, when I wasn't watching to make sure no one
accessed the db from the Vista machine it grew to over 200Mb again.

Anyone have any ideas?

Why not get someone qualified and capable to create a new, efficient
database?

[Randy Shore]

I've been reading your posts since 96 or 97, and I have always considered
you to be a helpful and respected member of this group. That's why I was
taken aback at the unresponsive way in which you answered my post.

You've worked with enough clients in your career to know they don't always
act on your suggestions. That being said, do you really think this bloat
problem in A2007 is directly related to that?

Don't you think it curious that compacting it in A2007 does nothing to
reduce the file size, but compacting it in A2003 does? I'm trying to figure
out what is happening (or not happening) in A2007 that causes the file to
bloat so rapidly when the same never happened when their office was A2003.
Could Vista be the random factor here?

I've talked with them about upgrading to SQL (which I honestly think they
require at this point), but they are unwilling or unable to bear the expense
of what would be a major conversion.

Friends? Please don't take me off of your Christmas card list over this.
"lyle fairfield" <ly******@yah00.cawrote in message
news:Xn************************@216.221.81.119...

"Randy Shore" <vt******************@roadrunner.comwrote in
news:47***********************@roadrunner.com:

I think it's unlikely that a qualified, capable developer would create the
non-split database you describe, and have multiple users run multiple
instances of it from one file stored on a network server.

I think that a large database of 40 tables, 40 forms (many with multiple
sub-forms), and 100s of reports is likely to be long-in-the-tooth and in
need of a rewrite, if not a replacement. The problem of bloat may provide
the nudge needed to modernize this. For such a large db, I think some
manifestation of MS-SQL Server should be considered.

I've been posting here since ninety-six or ninety-seven. When posters have
said, "Anyone have any ideas?" I have taken them at their word.

[lyle fairfield]

"Randy Shore" <vt******************@roadrunner.comwrote in
news:47***********************@roadrunner.com:

Don't you think it curious that compacting it in A2007 does nothing to
reduce the file size, but compacting it in A2003 does? I'm trying to
figure out what is happening (or not happening) in A2007 that causes
the file to bloat so rapidly when the same never happened when their
office was A2003. Could Vista be the random factor here?

I posted this in December 2006. One responder reported similar. It seems
that Access 2007 has or had some compacting issues.

"Today I asked Access 2007 to compact db1.mdb.

Access 2007 showed a message to me that I did not read carefully; my
recollection is that it said it could not save the compacted copy as
db1.mdb, and would save it as (something like) database1.mdb.

Fine, I thought, I'll just rename it (back to) db1.

But I cannot find any database of that name, or any new database of any
name on my hard drive and db1.mdb is gone. It is not in the recycle
bin.
Db1.mdb did not contain anything of importance, so it's not a great
problem. I could recover it, of course, if I needed to do so. Those for
whom the practice of recovering missing files is not common might be
more inconvenienced than I.

The problem would have been disagreeable with a different db.

I cannot duplicate this situation. None the less, I shall be careful
when compacting with Access 2007, at least for a while. If a database
has something essential in it, I'll make a copy first."

Friends? Please don't take me off of your Christmas card list over
this.

I missed last year, I know and I'm sorry, but I'll send off my usual
http://www.jacquielawson.com missive as soon as you explain how to
decrypt "vt******************@roadrunner.com".

[Tom van Stiphout]

On Thu, 3 Apr 2008 10:49:37 -0700, "Randy Shore"
<vt******************@roadrunner.comwrote:

I don't know what's the problem, other than to say I have not seen it
in similar configurations. Is a 200MB database really a problem? Does
it continue to bloat until the 2GB hard limit (you gotta have nerves
of steel to try that on a monolithic db)?

-Tom.

>I have a shared Access mdb that was originally written in A2000. The
database is large, around 40 tables, 40 forms (many with multiple
sub-forms), and 100s of reports. The database is not split, but runs off of
the LAN in the office with about 5 or 6 concurrent users. Recently the
office upgraded to A2007 but they are continuing to run in compatability
mode.

The problem is that with ordinary use, read/write operations through the
user forms only, the database quickly bloats from about 60Mb to over 200Mb.
There are no import/export actions, no make-table queries, nothing like that
in ordinary use. Compacting and Repairing the mdb from A2007 doesn't shrink
the mdb appreciably in size, but if I open the file with A2003 and compact
and repair it shrinks back down to 60Mb again.

The only wrinkle I can throw into the mix is that one of the users is using
Vista while the rest of the users are on XPpro. I tried to lock the Vista
user out of the db for a day to see if that affected the problem, and the
results are mixed. In the following 2 hours I didn't see the db grow much
in size, although it did grow from 60Mb to about 70Mb. In the next 2 hours,
when I wasn't watching to make sure no one accessed the db from the Vista
machine it grew to over 200Mb again.

Anyone have any ideas?

[Chuck Grimsby]

On Fri, 04 Apr 2008 12:23:05 GMT, lyle fairfield <ly******@yah00.ca>
wrote:

>Have you considered doing a "pseudo" compact (in Access 2007) as in

http://www.ffdba.com/downloads/Save_...ts_As_Text.htm

>If this works (TTBOMK it's never been tried on anything nearly as large
as the db you describe)

FWIW, I have similar code to yours that I've used on larger databases
then the one being discussed. It works, but I don't use it a lot.

Personally, I almost never worry about bloat. Access will expand as
it needs to, and won't when it doesn't. Constantly shrinking the db
just makes the mdb fragment on the disk faster.

--
Please Post Any Replies To This Message Back To the Newsgroup.
There are "Lurkers" around who can benefit by our exchange!

[SmartbizAustralia]

Seems like you might have something there.
Always wary of different versions of access at the same time and even
more so with Vista's dodgy file system, even after service pack 1.
Why on earth does it take minutes to load a sub-directory in a file
window when it takes micro seconds in xp!
That is probably a good reason to put the backend into sql server/sql
server express to take away issues with file access in vista!
I had nightmare developing in MsAccess on my vista system, which
improved when running MsAccess 2003 in a virtual, but if the file
wasn't also in the virtual the backend datases corrupted even with
single user access!

Would recommend splitting the database first to see if that makes any
difference.

If you are importing files into temporary tables or creating temporary
data before reporting or doing something else, then put those tables
in another altogether.

It would be nice to know why, ie is the situation above with data
coming into temporary tables...

Regards,
Tom Bizannes
http://www.macroview.com.au
Microsoft SQL Server and Access Development
Sydney, Australia