Database bloat - general discussion...

I've read a number of posts over the years that dealt with
the issue of database bloat. I'm wondering if anyone has
determined exactly what information comprises the bloat
when it does occur. Has anyone ever looked into that
and arrived at a conclusion?

Part-2 of the question is "Under what circumstances does
MS Access database bloat occur and what can be done
to circumvent it?"

re: Database bloat - general discussion...

Database bloat occurs when information or objects are deleted and the space
is not recovered. Compact and repair generally does a good job of recovering
vacated space, but does not always recover all of it. Sometimes you can
recover additional space by creating a new, empty database and importing the
objects from the bloated one; often, that new database expands the first
time, or first few times, it is used, so that it is as big, or nearly as
big, as the compacted database was before all the effort.

The best collection of detailed information and links on this subject that I
know about is at MVP Tony Toews' site,
http://www.granite.ab.ca/accsmstr.htm. (And, on many other subjects, too,
especially in the area of Access multiuser environment.)

Larry Linson
Microsoft Access MVP

"MLH" <CRCI@NorthState.net> wrote in message
news:6fk5k1d2r6nqs4gofjj1jurd2o1glu2pk3@4ax.com...[color=blue]
> I've read a number of posts over the years that dealt with
> the issue of database bloat. I'm wondering if anyone has
> determined exactly what information comprises the bloat
> when it does occur. Has anyone ever looked into that
> and arrived at a conclusion?
>
> Part-2 of the question is "Under what circumstances does
> MS Access database bloat occur and what can be done
> to circumvent it?"[/color]

re: Database bloat - general discussion...

"Larry Linson" <bouncer@localhost.not> wrote in
news:MDG1f.552$at1.287@trnddc05:
[color=blue]
> Database bloat occurs when information or objects are deleted and
> the space is not recovered.[/color]

That's not an entirely complete explanation.

New data pages can be allocated for edited objects that after the
edit exceed the space allocation that was available before the edit.
There is no way to predict when this would happen. Because of that,
I have never been a fan of editing saved QueryDefs, because that
leads to bloat if the changed QueryDef needs to be stored in new
data pages.

Of course, I'm also thinking in terms of A97 and before, where each
query is a record in the MSysObjects table. In A2K and beyond, the
whole project has to be resaved, which it would seem to me would
lead to even more bloat than in A97 and before.

And, what do you know -- one of the most commonly remarked-upon
differences between A2K and A97 is that A2K bloats much more.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

re: Database bloat - general discussion...

Thank-you sir. I'll have a look at MVP Tony Toews' site.
But your explanation and David's were quite helpful. I
gather there's only so much I can do to PREVENT bloat,
without seriously monitoring what I do and how I do it.
Its just a part of internal workings of Access. No other
specialty tools work any better than compaction built
in to Access. I can live with that.

re: Database bloat - general discussion...

MLH wrote:[color=blue]
> Thank-you sir. I'll have a look at MVP Tony Toews' site.
> But your explanation and David's were quite helpful. I
> gather there's only so much I can do to PREVENT bloat[/color]

What I like to do in most of my apps is run something like the following
in the on open event of my splash form or whatever form I have open with
the application. The controls, txtFE and txtBE are text boxes on my
splash form which show the user what the size of his files are:

Dim lngSizeFE As Long 'Size of front end
Dim lngSizeBe As Long 'size of data file

Dim strFE As String
Dim strBE As String

'file location information
'Front end

strFE = Access.CurrentDb.Name

lngSizeFE = FileLen(strFE)

Me.txtFE = "TCM Program Size: " & Format((lngSizeFE / 1000000),
"#.00") & " MB, " & strFE

'Back end: fGetLinkPath is a Dev Ashish function available from
'the Access Web at http://www.mvps.org/access/tables/tbl0007.htm

strBE = fGetLinkPath("TBL_GAME")

lngSizeBe = FileLen(strBE)

Me.txtBE = "TCM Current Data File Size: " & Format((lngSizeBe /
1000000), "#.00") & " MB, " & strBE

'Test FE file against a specific size - if greater than
'that size, have an "optimize program" menu item
'become visible. This is simply a copy of the standard
'Access Compact menu item with a caption that says:
'"OPTIMIZE (It is recommended you click this NOW)"

if lngSizeFE > 5000000 then '5 megs is the limit here...

Commandbars("mnuMain").Controls(4).Visible = True

Else

Commandbars("mnuMain").Controls(4).Visible = False

End If

I also have another "optimize" menu item along with a "optimize data
file" menu item in a "Utilities" menu on my main menu. The later menu
item involves compacting the back end (which I usually refer to as a
"data file" for my users). Since most of my projects are Oracle back
ends, I don't usually have the above txtBE data or an optimize back end
menu item.

I've found that when users occasionally have an OPTIMIZE NOW! menu item
yelling at them when they open their front end, that they click it and
take care of compacting their front end.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

re: Database bloat - general discussion...

There is one thing I have observed with respect to
bloat that I would like to share with the NG...

This applies to both A2.0 as well as A97 - I have NOT
experienced bloat in any of the runtime rollouts I've made.
That is a fact I've noticed. And, I've been quite grateful
for it having worked out just that way. Granted, in A97,
my rollouts are ALL MDE's and I'm sure that helps some-
how. There's not the constant addition and removal of
all object types in the runtime distro's. But I do quite a bit
of table/query creation/removal. And occasionally, I do
modify and report and save it These are my findings.