Absolutely stumped on front end bloating in Access 2007

[Brian Nelson]

Sorry for the long post, I've tried so hard to solve this one but I'm
just stuck!

We've had a multiuser .mdb file on a network share for years. I know
it's not ideal, but it's worked well from Access 2000 to 2003. Ever
since we upgraded to 2007, we've noticed some serious bloating.

When we were on 2003, after a fresh compact, the database would be at
about 20 MB. After normal use it would grow to about 25 MB and then
significantly slow down. It only grew a total of 6 MB in the last 8
months.

However, the very day we upgraded to Office 2007, the database bloated
up to 60 MB and counting. It doesn't ever seem to stop, I've seen it
as high as 90 MB. If I compact it, it goes back down to about 20 MB.

I have tried almost everything I can possibly think of; converted it
to 2007 format (accdb), split it, made an accde, decompiled and
recompiled the code, exported every object as text and rebuilt the
front-end, rebuilt the back-end from scratch, removed all code,
deleted all indexes, deleted all temp querydefs, etc. No matter what I
do, it bloats.

The bloating is happening in the front-end. After a fresh compact, the
front-end will be about 4 MB. It will easily grow to 20+ MB within an
hour. It also is affected by the number of users in it. The more
concurrent users, the faster it will bloat.

I stripped it down as much as physically possible, all the way down to
about a 1 MB back-end, and a 700k front-end. I asked 3 users to test
the front-end (shared accdb on the network drive), and within an hour
it blew up to nearly 3 MB (4x size).

I don't use temp tables, rarely use append queries, never delete
queries. I always close recordsets when I do any manipulation via VBA.
Always set variables to nothing when I'm done with them. Don't have
any embedded images or files.

I do, however, use several update queries. But it even bloated when I
completely disabled them (didn't even have them in the rebuilt front-
end).

I tried putting the front-end on someone's PC (with the hope to enable
Compact on Close), but the performance was terrible. We will
eventually get there when we move the back-end to SQL, but that's
probably months out.

I've seen a few complaints about this, is it a bug with 2007? It
doesn't seem normal that it should bloat so big so fast, especially in
the front-end.

Any thoughts? Thanks!

[Salad]

Brian Nelson wrote:

Sorry for the long post, I've tried so hard to solve this one but I'm
just stuck!

We've had a multiuser .mdb file on a network share for years. I know
it's not ideal, but it's worked well from Access 2000 to 2003. Ever
since we upgraded to 2007, we've noticed some serious bloating.

When we were on 2003, after a fresh compact, the database would be at
about 20 MB. After normal use it would grow to about 25 MB and then
significantly slow down. It only grew a total of 6 MB in the last 8
months.

However, the very day we upgraded to Office 2007, the database bloated
up to 60 MB and counting. It doesn't ever seem to stop, I've seen it
as high as 90 MB. If I compact it, it goes back down to about 20 MB.

I have tried almost everything I can possibly think of; converted it
to 2007 format (accdb), split it, made an accde, decompiled and
recompiled the code, exported every object as text and rebuilt the
front-end, rebuilt the back-end from scratch, removed all code,
deleted all indexes, deleted all temp querydefs, etc. No matter what I
do, it bloats.

The bloating is happening in the front-end. After a fresh compact, the
front-end will be about 4 MB. It will easily grow to 20+ MB within an
hour. It also is affected by the number of users in it. The more
concurrent users, the faster it will bloat.

I stripped it down as much as physically possible, all the way down to
about a 1 MB back-end, and a 700k front-end. I asked 3 users to test
the front-end (shared accdb on the network drive), and within an hour
it blew up to nearly 3 MB (4x size).

I don't use temp tables, rarely use append queries, never delete
queries. I always close recordsets when I do any manipulation via VBA.
Always set variables to nothing when I'm done with them. Don't have
any embedded images or files.

I do, however, use several update queries. But it even bloated when I
completely disabled them (didn't even have them in the rebuilt front-
end).

I tried putting the front-end on someone's PC (with the hope to enable
Compact on Close), but the performance was terrible. We will
eventually get there when we move the back-end to SQL, but that's
probably months out.

I've seen a few complaints about this, is it a bug with 2007? It
doesn't seem normal that it should bloat so big so fast, especially in
the front-end.

Any thoughts? Thanks!

I don't know how one can work with an application not split into
front/backend after A97 unless you never make updates.

I suggest you go to Tony Toews site and read his stuff on splitting
mdbs. What I found helpful, besides a persistent link to the backend,
was making sure my filenames followed the 8.3 DOS format. That really
speeded it up for me.

Link to Tony's site. http://www.granite.ab.ca/accsmstr.htm.
http://www.granite.ab.ca/access/performancefaq.htm

Link to Allen Browne's site http://www.allenbrowne.com/tips.html. Some
2007 stuff. http://www.allenbrowne.com/tips.html

[Brian Nelson]

On Aug 11, 1:49*pm, Salad <[e-mail]:

Brian Nelson wrote:

Sorry for the long post, I've tried so hard to solve this one but I'm
just stuck!

We've had a multiuser .mdb file on a network share for years. I know
it's not ideal, but it's worked well from Access 2000 to 2003. Ever
since we upgraded to 2007, we've noticed some serious bloating.

When we were on 2003, after a fresh compact, the database would be at
about 20 MB. After normal use it would grow to about 25 MB and then
significantly slow down. It only grew a total of 6 MB in the last 8
months.

However, the very day we upgraded to Office 2007, the database bloated
up to 60 MB and counting. It doesn't ever seem to stop, I've seen it
as high as 90 MB. If I compact it, it goes back down to about 20 MB.

I have tried almost everything I can possibly think of; converted it
to 2007 format (accdb), split it, made an accde, decompiled and
recompiled the code, exported every object as text and rebuilt the
front-end, rebuilt the back-end from scratch, removed all code,
deleted all indexes, deleted all temp querydefs, etc. No matter what I
do, it bloats.

The bloating is happening in the front-end. After a fresh compact, the
front-end will be about 4 MB. It will easily grow to 20+ MB within an
hour. It also is affected by the number of users in it. The more
concurrent users, the faster it will bloat.

I stripped it down as much as physically possible, all the way down to
about a 1 MB back-end, and a 700k front-end. I asked 3 users to test
the front-end (shared accdb on the network drive), and within an hour
it blew up to nearly 3 MB (4x size).

I don't use temp tables, rarely use append queries, never delete
queries. I always close recordsets when I do any manipulation via VBA.
Always set variables to nothing when I'm done with them. Don't have
any embedded images or files.

I do, however, use several update queries. But it even bloated when I
completely disabled them (didn't even have them in the rebuilt front-
end).

I tried putting the front-end on someone's PC (with the hope to enable
Compact on Close), but the performance was terrible. We will
eventually get there when we move the back-end to SQL, but that's
probably months out.

I've seen a few complaints about this, is it a bug with 2007? It
doesn't seem normal that it should bloat so big so fast, especially in
the front-end.

Any thoughts? Thanks!

I don't know how one can work with an application not split into
front/backend after A97 unless you never make updates.

I suggest you go to Tony Toews site and read his stuff on splitting
mdbs. *What I found helpful, besides a persistent link to the backend,
was making sure my filenames followed the 8.3 DOS format. *That really
speeded it up for me.

Link to Tony's site. *http://www.granite.ab.ca/accsmstr.ht...ormancefaq.htm

Link to Allen Browne's sitehttp://www.allenbrowne.com/tips.html. *Some
2007 stuff. *http://www.allenbrowne.com/tips.html- Hide quoted text -

- Show quoted text -

Oh yeah, been to Tony's and Allen's sites many times. :-)

I never had any problems with the shared .mdb, and it was easy enough
to update, so I never had a need to split it. But regardless, I DID
try splitting it to confront this bloating problem and it didn't help.

I never thought about the 8.3 format, but wouldn't that confuse
Access, being that the new file extension is .accdb?

I'm sure that the new version of Access doesn't like something I've
done in the past, be it a new reserved field name, a deprecated
function, or something along those lines. Short of a total rewrite, I
can't seem to find what it doesn't like.

I've seen several threads on various sites describing the same
problem, but so far nobody seems to be able to figure out what's
causing it.

[Tony Toews [MVP]]

Brian Nelson <bn************@gmail.comwrote:

>When we were on 2003, after a fresh compact, the database would be at
about 20 MB. After normal use it would grow to about 25 MB and then
significantly slow down. It only grew a total of 6 MB in the last 8
months.

That was about my experience as well with my clients. Although it was more like a
200 Mb BE would grow to 225 Mb in the first day after compacting and grow 100 kb per
day until the next compacting.

>However, the very day we upgraded to Office 2007, the database bloated
up to 60 MB and counting. It doesn't ever seem to stop, I've seen it
as high as 90 MB. If I compact it, it goes back down to about 20 MB.

I suspect you're not going to get an answer other than splitting. I can see the
ACE (upgrade of DAO) being less efficient at saving disk space than Jet 4.0 or Jet
3.5.

For example if a page containing multiple records is updated then all the records in
that page are moved to other pages where they are individually stored. Thus what
was on one 4 kb page now takes 20 4 kb pages. Note that I'm not stating this is the
case, just one possibility.

Really I have no idea.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[bcap]

I always set up environments where users never open the same front-end file
twice.

What I mean by this is that I write a vbscript file, and put a shortcut to
it on the user's desktop, which launches the application by going through
this process:

There are actually three files involved: the main master (on a file
server), a local master copy, and the local run copy. The process is thus:

Does the local master exist? If not, copy the main master to the local
master.
Is the local master older than the main master? If so, copy the main master
to the local master.
Copy the local master to the local run copy.
Launch the local run copy.

This ensures that (i) users always get a "fresh" front-end when they launch
the system, thereby eliminating problems with corruption and bloat (ii) new
versions get automatically distributed simply by replacing the main master
and (iii) file copies across the network only occur when there is a new
version.

It does have the *dis*advantage that any changes users make to their
environment, where such changes are stored in the FE file itself, are lost
each time they re-start the system.

As for performance issues with a local front-end against an Access back-end,
I can't help because I invariably use SQL Server back-ends.

"Brian Nelson" <bn************@gmail.comwrote in message
news:5b**********************************@m44g2000 hsc.googlegroups.com...

Sorry for the long post, I've tried so hard to solve this one but I'm
just stuck!

We've had a multiuser .mdb file on a network share for years. I know
it's not ideal, but it's worked well from Access 2000 to 2003. Ever
since we upgraded to 2007, we've noticed some serious bloating.

When we were on 2003, after a fresh compact, the database would be at
about 20 MB. After normal use it would grow to about 25 MB and then
significantly slow down. It only grew a total of 6 MB in the last 8
months.

However, the very day we upgraded to Office 2007, the database bloated
up to 60 MB and counting. It doesn't ever seem to stop, I've seen it
as high as 90 MB. If I compact it, it goes back down to about 20 MB.

I have tried almost everything I can possibly think of; converted it
to 2007 format (accdb), split it, made an accde, decompiled and
recompiled the code, exported every object as text and rebuilt the
front-end, rebuilt the back-end from scratch, removed all code,
deleted all indexes, deleted all temp querydefs, etc. No matter what I
do, it bloats.

The bloating is happening in the front-end. After a fresh compact, the
front-end will be about 4 MB. It will easily grow to 20+ MB within an
hour. It also is affected by the number of users in it. The more
concurrent users, the faster it will bloat.

I stripped it down as much as physically possible, all the way down to
about a 1 MB back-end, and a 700k front-end. I asked 3 users to test
the front-end (shared accdb on the network drive), and within an hour
it blew up to nearly 3 MB (4x size).

I don't use temp tables, rarely use append queries, never delete
queries. I always close recordsets when I do any manipulation via VBA.
Always set variables to nothing when I'm done with them. Don't have
any embedded images or files.

I do, however, use several update queries. But it even bloated when I
completely disabled them (didn't even have them in the rebuilt front-
end).

I tried putting the front-end on someone's PC (with the hope to enable
Compact on Close), but the performance was terrible. We will
eventually get there when we move the back-end to SQL, but that's
probably months out.

I've seen a few complaints about this, is it a bug with 2007? It
doesn't seem normal that it should bloat so big so fast, especially in
the front-end.

Any thoughts? Thanks!

[Salad]

Brian Nelson wrote:

On Aug 11, 1:49 pm, Salad <[e-mail]:

I never had any problems with the shared .mdb, and it was easy enough
to update, so I never had a need to split it. But regardless, I DID
try splitting it to confront this bloating problem and it didn't help.

I never thought about the 8.3 format, but wouldn't that confuse
Access, being that the new file extension is .accdb?

You mentioned performance degradation. You need to read the stuff at
Allen's and Tony's sites to combat that. Who knows, maybe you have
track autocorrect (or is it autoname) on. No persistent link. Long
file names. Both Tony and I have experienced performance degradation
after splitting but following the steps at both sites should minimize
the impact.

>
I'm sure that the new version of Access doesn't like something I've
done in the past, be it a new reserved field name, a deprecated
function, or something along those lines. Short of a total rewrite, I
can't seem to find what it doesn't like.

When in A97 and using a non-split database I got a lot of bloat. After
splitting, very little if any.

>
I've seen several threads on various sites describing the same
problem, but so far nobody seems to be able to figure out what's
causing it.

I'd follow the advice by others who have been there, done that.

[Brian Nelson]

Thanks Tony, bcap, and Salad, for the replies. This is more of a reply
to the thread than Tony (I apologize if I'm not doing the usenet thing
right).

I'm by no means against splitting, it was the first thing I did. I
always knew I -should- have done it, but if it ain't broke dont fix
it...

My problem with the splitting was the performance got much worse than
even the shared .mdb, in spite of the persistent connection set up and
autocorrect turned off. To add insult to injury, it continued to bloat
(only in the front-end). The split front-end was about 4 MB, easily
growing to 20+ MB within an hour. I don't know, maybe it was just
network issues at the time, I guess I could try it again.

The frustrating thing is that most people shrug it off as normal
behavior for Access 2007 -- but we have another, very similar database
(another shared .mdb file) that does NOT do this.

By the way, we are actively working towards getting it properly split
onto a SQL Server back-end. We were hoping to be there already, but
our IS department is delaying the project.

That's a really neat idea bcap, I was pondering how to automate the
process of updating the FE (apologies to Tony, I've heard you have a
wonderful FE updater program, I just haven't had the chance to look
into it). I guess I'll be doing something along these lines sooner
than later...

[Tony Toews [MVP]]

Brian Nelson <bn************@gmail.comwrote:

>My problem with the splitting was the performance got much worse than
even the shared .mdb, in spite of the persistent connection set up and
autocorrect turned off. To add insult to injury, it continued to bloat
(only in the front-end). The split front-end was about 4 MB, easily
growing to 20+ MB within an hour. I don't know, maybe it was just
network issues at the time, I guess I could try it again.

Silly question. Did each user get their own copy of the FE?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[Tom van Stiphout]

On Tue, 12 Aug 2008 07:04:40 -0700 (PDT), Brian Nelson
<bn************@gmail.comwrote:

Fortunately it is impossible these days to buy a harddrive less than a
thousand times as large as that database, so we shrug and move on to
issues we have control over.

-Tom.
Microsoft Access MVP

>Thanks Tony, bcap, and Salad, for the replies. This is more of a reply
to the thread than Tony (I apologize if I'm not doing the usenet thing
right).

I'm by no means against splitting, it was the first thing I did. I
always knew I -should- have done it, but if it ain't broke dont fix
it...

My problem with the splitting was the performance got much worse than
even the shared .mdb, in spite of the persistent connection set up and
autocorrect turned off. To add insult to injury, it continued to bloat
(only in the front-end). The split front-end was about 4 MB, easily
growing to 20+ MB within an hour. I don't know, maybe it was just
network issues at the time, I guess I could try it again.

The frustrating thing is that most people shrug it off as normal
behavior for Access 2007 -- but we have another, very similar database
(another shared .mdb file) that does NOT do this.

By the way, we are actively working towards getting it properly split
onto a SQL Server back-end. We were hoping to be there already, but
our IS department is delaying the project.

That's a really neat idea bcap, I was pondering how to automate the
process of updating the FE (apologies to Tony, I've heard you have a
wonderful FE updater program, I just haven't had the chance to look
into it). I guess I'll be doing something along these lines sooner
than later...

[david.triplett]

One of the methods that you can use if you have a FE is to set the
properties of the database to read only. This does allow changes to
the file (bloating) but still allows the FE to interact with the BE.

[Brian Nelson]

On Aug 12, 2:10*pm, "Tony Toews [MVP]" <[e-mail]:

Silly question. * Did each user get their own copy of the FE?

Tony
--
Tony Toews, Microsoft Access MVP
* *Please respond only in the newsgroups so that others can
read the entire thread of messages.
* *Microsoft Access Links, Hints, Tips & Accounting Systems athttp://www.granite.ab.ca/accsmstr.htm
* *Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/

I took my laptop and a stopwatch home last night and tried it both
ways with freshly compacted databases. (I have only a 1.5 MB
connection at home, which may explain the numbers)

Non-split, shared .accdb on network drive (via VPN) - 2 minutes to
load, 1 minute to swap between records.
Split, local FE installed on my hard drive, BE being on network drive
(via VPN) - 4 minutes to load, 5 minutes to swap between records!!!

Obviously these numbers aren't very convincing to my bosses that we
need to split.

Per your performance page, I do have a form in the FE bound to a table
in the BE to force a "persistent connection". It opens as hidden when
the database opens, but something tells me I'm not doing it right. Are
there obvious form properties that I should be setting or not setting
(e.g. record locking?).

I was going to try the recordset method next, but I may not get time
to do it until tomorrow.
BTW - Tom, very true about the hard drive situation, but we have a lot
of users working remotely, many of whom can't even get into the
database now due to the bloating (people give up after 10 minutes or
so of loading).

[David W. Fenton]

Brian Nelson <bn************@gmail.comwrote in
news:b0**********************************@i76g2000 hsf.googlegroups.co
m:

Non-split, shared .accdb on network drive (via VPN)

Testing over a VPN negates any results. You should be testing over a
LAN, since you never run an Access app with a Jet back end over a
VPN/WAN (unless you're a masochist, or just bloody stupid).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

Brian Nelson <bn************@gmail.comwrote in
news:b0**********************************@i76g2000 hsf.googlegroups.co
m:

BTW - Tom, very true about the hard drive situation, but we have a
lot of users working remotely, many of whom can't even get into
the database now due to the bloating (people give up after 10
minutes or so of loading).

Windows Terminal Server is the best solution, I'd say.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Brian Nelson]

On Aug 13, 7:03*pm, "David W. Fenton" <[e-mail]>
wrote:

Brian Nelson <[e-mail] innews:b0**********************************@i76g20 00hsf.googlegroups.co
m:

Non-split, shared .accdb on network drive (via VPN)

Testing over a VPN negates any results. You should be testing over a
LAN, since you never run an Access app with a Jet back end over a
VPN/WAN (unless you're a masochist, or just bloody stupid).

--
David W. Fenton * * * * * * * * *http://www.dfenton.com/
usenet at dfenton dot com * *http://www.dfenton.com/DFA/

I'd also like every user to be local, have a SQL Server back-end, and
a .NET front-end, but it is what it is. Business needs and department
resources don't always give you what you want, the best you can do is
optimize it for your environment.

Terminal services is an option on the table, but there's a cycle of
justification, resource allocation, Office licensing, etc. In the
interest of time, I would prefer to get the split database working
reasonably well.

[David W. Fenton]

Brian Nelson <bn************@gmail.comwrote in
news:eb**********************************@56g2000h sm.googlegroups.com
:

On Aug 13, 7:03*pm, "David W. Fenton"
<[e-mail]:

>Brian Nelson <[e-mail]
innews:b010fd06-dcd9-4cf4-b

12**************@i76g2000hsf.googlegroups.co

>m:

Non-split, shared .accdb on network drive (via VPN)

Testing over a VPN negates any results. You should be testing
over a LAN, since you never run an Access app with a Jet back end
over a VPN/WAN (unless you're a masochist, or just bloody
stupid).

I'd also like every user to be local, have a SQL Server back-end,
and a .NET front-end, but it is what it is. Business needs and
department resources don't always give you what you want, the best
you can do is optimize it for your environment.

Running an Access app across a VPN when the back end is Jet is a
recipe for disaster, and huge frustration for the users. If the data
is worth anything at all, you should be scrambling to get out of
that situation -- it's a no-go for Access/Jet apps, and always has
been.

Terminal services is an option on the table, but there's a cycle
of justification, resource allocation, Office licensing, etc. In
the interest of time, I would prefer to get the split database
working reasonably well.

If you have to deploy across a WAN, you're wasting your time with
the split database -- it will *never* work "reasonably well," and it
will *always* be extremely dangerous for your data.

Terminal Services is the quickest way to fix the performance
problems. It will also make your data safer and make administration
much, much easier.

It should be a no-brainer.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/