Database bloat in shared A2007 DB

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?

Re: Database bloat in shared A2007 DB

"Randy Shore" wrote in
news:47f518b6$0$22854$4c368faf@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?

Re: Database bloat in shared A2007 DB

That was totally uncalled for, Lyle.

If you want to discuss the complexity of my model I'ld be happy to oblige,
but suffice it to say that the model is not the cause of this new bloating
problem in a database that has perfomed the tasks for which it was designed
for the past 5 years without any problem, and that with A2007 this has
suddenly become a problem.

(When did Lyle become a troll in this group?)


"lyle fairfield" wrote in message
news:Xns9A7596D2ABD1B6666646261@216.221.81.119...
> "Randy Shore" wrote in
> news:47f518b6$0$22854$4c368faf@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?

Re: Database bloat in shared A2007 DB

"Randy Shore" wrote in message
news:47f52c8f$0$22852$4c368faf@roadrunner.com...
> That was totally uncalled for, Lyle.
>
> If you want to discuss the complexity of my model I'ld be happy to oblige,
> but suffice it to say that the model is not the cause of this new bloating
> problem in a database that has perfomed the tasks for which it was
> designed for the past 5 years without any problem, and that with A2007
> this has suddenly become a problem.
>
> (When did Lyle become a troll in this group?)

Lyle's perfectly capable of 'taking up for himself' but perhaps it was your
comment that the DB was multiuser, but not split, led him to think it might
use some TLC.

Surely you are aware that all the experienced hands at working in the
multiuser environment have advised here, over and over, that you increase
the possibility of corruption if you have multiple users, though it may run
for years, you just cannot predict what innocent-appearing change may
trigger frequent corruption, which may or may not be repairable.

I don't have a moment's doubt that introducing one or more users with A2007
contributed to, if not caused, the problem. As I do very little with A2007
at this point, someone else would have to pursue that potential problem with
you.

And, interestingly, Lyle has more experience with A2007 (and Vista, IIRC)
than many of us.

As I don't see that he indicated he was "peddling his services", maybe you
should have pursued the matter a little more before branding him a "troll".

At times, of course, it appears that "tact" is not always Lyle's strongest
point. :-)

Larry Linson
Microsoft Office Access MVP

Re: Database bloat in shared A2007 DB

"Randy Shore" wrote in
news:47f52c8f$0$22852$4c368faf@roadrunner.com:

> That was totally uncalled for, Lyle.
>
> If you want to discuss the complexity of my model I'ld be happy to
> oblige, but suffice it to say that the model is not the cause of this
> new bloating problem in a database that has perfomed the tasks for
> which it was designed for the past 5 years without any problem, and
> that with A2007 this has suddenly become a problem.
>
> (When did Lyle become a troll in this group?)

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.

Re: Database bloat in shared A2007 DB

Thank you Larry for your comments. I don't want to get into a flame-war
with Lyle, whose contributions to this forum I have read and respected for
years.

Maybe I was a little fast to pull the Troll trigger, but as my momma always
told me, "A troll is as a troll does." She also used to tell me something
about life being like a box of chocolates, but that story is for a different
time.

I realize that I'm not helping my developer cred any by comparing myself to
Forrest Gump, but to be honest I'm well aware that multi-user apps are not
ideal. The User signs my checks, and that's the way they wanted it. This
environment is read-intensive, and the chances of write conflicts are
exceedingly low. No matter how you slice it, this bloat problem is a new
one that appeared with their introduction of A2007.

I still hope that some of the MVPs of this group might consider my problem
and contribute some usable information to help me solve it.


"Larry Linson" wrote in message
news:g4bJj.18750$bC6.17452@trnddc08...
>
> "Randy Shore" wrote in message
> news:47f52c8f$0$22852$4c368faf@roadrunner.com...
>> That was totally uncalled for, Lyle.
>>
>> If you want to discuss the complexity of my model I'ld be happy to
>> oblige, but suffice it to say that the model is not the cause of this new
>> bloating problem in a database that has perfomed the tasks for which it
>> was designed for the past 5 years without any problem, and that with
>> A2007 this has suddenly become a problem.
>>
>> (When did Lyle become a troll in this group?)
>
> Lyle's perfectly capable of 'taking up for himself' but perhaps it was
> your comment that the DB was multiuser, but not split, led him to think it
> might use some TLC.
>
> Surely you are aware that all the experienced hands at working in the
> multiuser environment have advised here, over and over, that you increase
> the possibility of corruption if you have multiple users, though it may
> run for years, you just cannot predict what innocent-appearing change may
> trigger frequent corruption, which may or may not be repairable.
>
> I don't have a moment's doubt that introducing one or more users with
> A2007 contributed to, if not caused, the problem. As I do very little
> with A2007 at this point, someone else would have to pursue that potential
> problem with you.
>
> And, interestingly, Lyle has more experience with A2007 (and Vista, IIRC)
> than many of us.
>
> As I don't see that he indicated he was "peddling his services", maybe you
> should have pursued the matter a little more before branding him a
> "troll".
>
> At times, of course, it appears that "tact" is not always Lyle's strongest
> point. :-)
>
> Larry Linson
> Microsoft Office Access MVP
>
>
>

Re: Database bloat in shared A2007 DB

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" wrote in message
news:Xns9A75B15A74D776666646261@216.221.81.119...
> "Randy Shore" wrote in
> news:47f52c8f$0$22852$4c368faf@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.

Re: Database bloat in shared A2007 DB

"Randy Shore" wrote in
news:47f550cb$0$22810$4c368faf@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 "vtashore.TakeThisOut@roadrunner.com".

Re: Database bloat in shared A2007 DB

Yeah, I read that post when I was researching this problem, but it doesn't
seem to be the same problem that I have. To be honest, when I read that
post I was thinking that the user had not set the default path in Access to
the location he had used in his previous version, and that he would probably
fine the db hiding in MyDocuments under his User path.

As for decrypting vtashore.TakeThisOut@roadrunner.com, take out the
".TakeThisOut" and the remainder is my email.

Eagerly awaiting the Christmas season . . . .

Randy Shore

"lyle fairfield" wrote in message
news:Xns9A75B79CE73F26666646261@216.221.81.119...
> "Randy Shore" wrote in
> news:47f550cb$0$22810$4c368faf@roadrunner.com:
>> 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 "vtashore.TakeThisOut@roadrunner.com".

Re: Database bloat in shared A2007 DB

On Thu, 3 Apr 2008 10:49:37 -0700, "Randy Shore"
wrote:

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?
>

Re: Database bloat in shared A2007 DB

I don't have nerves of steel (or big brass one, either.)

I do see a performance hit as it bloats, and that is resolved when I compact
it in A2003 and it shrinks in size.


"Tom van Stiphout" wrote in message
news:htabv3t7l076eheksoupfgc38lhgnr828d@4ax.com...
> On Thu, 3 Apr 2008 10:49:37 -0700, "Randy Shore"
> wrote:
>
> 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?
>>

Re: Database bloat in shared A2007 DB

"Randy Shore" wrote in
news:47f6175e$0$6521$4c368faf@roadrunner.com:

> I don't have nerves of steel (or big brass one, either.)
>
> I do see a performance hit as it bloats, and that is resolved when I
> compact it in A2003 and it shrinks in size.



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

http://www.ffdba.com/downloads/Save_MDB_Objects_As_Text.htm

?

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

- make the db smaller than a normal compact (the new smaller db can be
found in the subfolder created by the code; sometimes it’s much smaller;
sometimes it isn't.)

- identify problems that Access 2007 is having with any of the 2003
objects, as Access 2007 will error out on reloading the object from text
unless its super pure. (So will Ac2003 should you try it there.)

Of course, you'd mess with it on a safe copy only. Me? I'd mess with it
on a third or fourth safe copy.

Then again, are the data confidential? Will it zip down to something
under 10 Megs? (7z - [freeware] sometimes makes a .7z file one third the
size of a standard zip ... and three times as fast. Maybe if this is a
common problem that's going to recur for many, then regulars here might
like to examine the thing. But of course, that may not be allowable or
desirable from your point of view.

Re: Database bloat in shared A2007 DB

On Fri, 04 Apr 2008 12:23:05 GMT, lyle fairfield
wrote:

>Have you considered doing a "pseudo" compact (in Access 2007) as in
>
>http://www.ffdba.com/downloads/Save_MDB_Objects_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!

Re: Database bloat in shared A2007 DB

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