I'm using a backend that's been around for years before I joined the
company. It kind of grew exponentially and has some design problems
including large unused tables some temporary tables. It compacts to
150Mb, but after a day it's up to 300Mb and it gros steadily from
there. I need to:
(a) reduce the size
(b) reduce the bloat factor

Apart from moving the temporary tables to front end (which can't
possibly account for all of the this bloat) and emptying/deleting
unused tables does anybody have any helpful suggestions? Any good
online guides to common causes of backend bloat etc?

Also if anyone has any VB functions or queries for analysing all the
table stats - size, usage etc that would be very helpful.

Thanks a million

Bill

The best collection of links and information on Access in a multiuser
environment that I have found is at MVP Tony Toews' site
http://www.granite.ab.ca/accsmstr.htm.

Temporary tables in either your front- or back-end will cause bloat, but
there is an easy answer to that issue... create a temporary database, create
the temporary table in that database, link to it, use it, unlink, and use
the Kill statement to delete the temporary database. I believe Tony has an
example of that.

All Access databases are subject to "growing" -- space occupied by deleted
records and deleted objects is not recovered; it just stays there until you
do a Compact on the database.

Larry Linson
Microsoft Access MVP

"BillCo" <coleman.bill@gmail.com> wrote in message
news:1120212868.178881.18050@g49g2000cwa.googlegro ups.com...[color=blue]
>
> I'm using a backend that's been around for years before I joined the
> company. It kind of grew exponentially and has some design problems
> including large unused tables some temporary tables. It compacts to
> 150Mb, but after a day it's up to 300Mb and it gros steadily from
> there. I need to:
> (a) reduce the size
> (b) reduce the bloat factor
>
> Apart from moving the temporary tables to front end (which can't
> possibly account for all of the this bloat) and emptying/deleting
> unused tables does anybody have any helpful suggestions? Any good
> online guides to common causes of backend bloat etc?
>
> Also if anyone has any VB functions or queries for analysing all the
> table stats - size, usage etc that would be very helpful.
>
> Thanks a million
>
> Bill
>[/color]

"Larry Linson" <bouncer@localhost.not> wrote in
news:Wnjxe.5278$mr4.693@trnddc05:
[color=blue]
> Temporary tables in either your front- or back-end will cause
> bloat, but there is an easy answer to that issue... create a
> temporary database, create the temporary table in that database,
> link to it, use it, unlink, and use the Kill statement to delete
> the temporary database. I believe Tony has an example of that.[/color]

Let me vote for *not* killing the created temp MDB -- just re-use
it.

If you're concerned about bloat, then pull an empty copy down from
the server to replace the "used" copy.

I used to delete the temp.mdb on exit and copy it from an empty
version on application load, but eventually stopped bothering. The
bloat just isn't an issue on today's workstations, which have 10s of
GBs of free disk space.

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

"BillCo" <coleman.bill@gmail.com> wrote in
news:1120212868.178881.18050@g49g2000cwa.googlegro ups.com:
[color=blue]
>
> I'm using a backend that's been around for years before I joined
> the company. It kind of grew exponentially and has some design
> problems including large unused tables some temporary tables. It
> compacts to 150Mb, but after a day it's up to 300Mb and it gros
> steadily from there. I need to:
> (a) reduce the size
> (b) reduce the bloat factor
>
> Apart from moving the temporary tables to front end (which can't
> possibly account for all of the this bloat) and emptying/deleting
> unused tables does anybody have any helpful suggestions? Any good
> online guides to common causes of backend bloat etc?[/color]

As Larry says, do *not* put the temp tables in the front end, but in
a separate MDB on the workstation alongside the front end.

If you have issues with relinking the front end to multiple back
ends, you could try my Reconnect code that is specifically designed
for handling multiple back ends (it handles only MDB links, though I
had laid the groundwork in the design for handling Excel
spreadsheets and dBase files). It's here:

http://www.dfenton.com/DFA/download/...Reconnect.html
[color=blue]
> Also if anyone has any VB functions or queries for analysing all
> the table stats - size, usage etc that would be very helpful.[/color]

The amount of your bloat, 150 to 300, sounds like not all that much
to me. I've seen much worse. I would suggest that it could very
likely all be due to the temp tables.

Now, 150 sounds pretty big for the compacted size, unless you've got
100s of thousands of records in all your tables. Here are some
things to reduce bloat:

1. remove duplicate indexes. Here are some of the sources of
unnecessary duplicate indexes:

a. the setting that automatically indexes ID fields, etc.

b. creating a PK and indexing it and then converting it to a PK
field, which adds a second index.

c. creating relationships, which automatically creates hidden
indexes.

d. overzealous developers who put an index on every possible
field, instead of limiting it to the fields that are used for
searching/sorting/joining.

e. you may want to remove indexes on fields with few unique
values (which is called a "sparsely populated" index), or a high
proportion of NULLs, but I've found that this advice often leads
to significant slowdowns in WHERE clauses on those fields. A
perfect example would be a Boolean field, but I've found that
leaving the indexes on those fields gives a noticeable speed
boost, and that with enough records involved, removing them leads
to significant problematic loss of speed.

f. on compound keys, remove any index declared only on the first
field of the compound PK, because Jet will use the compound key's
index for searches on the first field (because it's going to be
stored in the order of the first column). So, if it's a
two-column compound key (i.e., with a compound index on the two
fields), you only need to declare an explicit index on the second
field. So, the number of indexes is always N-1, where N is the
number of columns in the compound key.

You can see what indexes there are on each table by using the
documentation wizard. The first time I did this, I was shocked by
what I found the first time I did this, and ended up reducing a 20MB
back end to under 15MBs, just by removing the duplicate and unused
indexes.

2. create a fresh new MDB and import all the tables and
relationships. You might even want to go all the way and create the
tables and relationships from scratch in the new MDB and then append
the data. I can't think of a scenario where that would be better,
except my sense of voodoo about these things -- it always seems that
there are things lurking behind the scenes that I know nothing about
that get dragged along when you do an import.

However, that's much more likely with forms/reports/etc. because
they have compiled modules. With module-bearing objects, it's best
to decompile and compact before importing, since that would leave
the objects with no compiled code at all. And, best of all for this
is to use Application.SaveAsText and Application.LoadFromText, but
that's not for data.

3. if the back end is replicated and replication is no longer used,
unreplicated it. Here are some links with tools to help:

http://trigeminal.com/lang/1033/util...p?ItemID=-1#11

http://trigeminal.com/lang/1033/utility.asp?ItemID=7#7

For Access97, there's:

http://support.microsoft.com/kb/q153526/

And also:

http://www.pacificdb.com.au/MVP/Code/UnReplicate.htm

4. as you already, said, remove unused tables, but you should also
evaluate if the current table structure is properly normalized. If
there's lots of repeated data, it can add a lot to the size of
tables.

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

"BillCo" <coleman.bill@gmail.com> wrote in message
news:1120212868.178881.18050@g49g2000cwa.googlegro ups.com...[color=blue]
>
> I'm using a backend that's been around for years before I joined the
> company. It kind of grew exponentially and has some design problems
> including large unused tables some temporary tables. It compacts to
> 150Mb, but after a day it's up to 300Mb and it gros steadily from
> there. I need to:
> (a) reduce the size
> (b) reduce the bloat factor
>
> Apart from moving the temporary tables to front end (which can't
> possibly account for all of the this bloat) and emptying/deleting
> unused tables does anybody have any helpful suggestions? Any good
> online guides to common causes of backend bloat etc?
>
> Also if anyone has any VB functions or queries for analysing all the
> table stats - size, usage etc that would be very helpful.
>
> Thanks a million
>
> Bill
>[/color]


Hi Bill--

File bloat is an inherent defect in Access. There's no cure for Access file
bloat. Even Access MDE files with no tables will bloat. Microsoft has
acknowledged the file bloat problem in every version of Access and has not
come up with a solution yet. Don't waste your time trying the silly
suggestions given in this thread. Just compact the database whenever you
can and forget it.

--Mast

"Mast Eckhart" <nospam@nospam.net>
[color=blue]
> File bloat is an inherent defect in Access.[/color]

Database growth is a feature of Access. "Bloat" implies something more than
normal growth.
[color=blue]
> There's no cure for Access file bloat.[/color]

Actually, a lot of the "silly suggestions" you disdain will slow database
growth so that it doesn't become "bloat"
[color=blue]
> Even Access MDE files with no tables will
> bloat. Microsoft has acknowledged the file
> bloat problem in every version of Access
> and has not come up with a solution yet.[/color]

Except they have had the solution you recommend, Compact, all along.
[color=blue]
> Don't waste your time trying the silly
> suggestions given in this thread. Just
> compact the database whenever you
> can and forget it.[/color]

See above. Those silly suggestions are useful for managing database growth
due to Access' method of recovering space used by deleted objects.

Larry Linson
Microsoft Access MVP

Thanks David, Larry and Mast for your advice! I dont think Indexing is
the cause - It seems to be a combination of redundant data and misuse
of temp tables. However the undocumented and sprawling mess of vb code
and macros makes it difficult/impossible to start whacking it into a
proper shape with any certainty of it all working when i'm done :(
....so i might just be stuck with daily compacting. I'll try removing
the excess tables. Then I'l re-create the tables in a new mdb and copy
the info across - anyone have any good code for this so i dont have to
re-invent the wheel?

"BillCo" <coleman.bill@gmail.com> wrote in
news:1120462045.389612.255700@g44g2000cwa.googlegr oups.com:
[color=blue]
> Thanks David, Larry and Mast for your advice! . ..[/color]

Ignore what "Mast" said, as it's completely false and erroneous
information.

Indeed, the post has all the fingerprints of our resident troll, Don
Mellon, who seems to have no life, other than trying to annoy people
in this newsgroup by posting erroneous and misleading information to
the newsgroup in the hopes (I assume) of poisoning the well so that
the newsgroup becomes unreliable.
[color=blue]
> . . . I dont think
> Indexing is the cause - It seems to be a combination of redundant
> data and misuse of temp tables. . . .[/color]

Well, don't underestimate the amount of space taken up by redundant
indexes. Also, keep in mind that those indexes have to be kept
up-to-date, which means that every write to an indexed field is
going to generate writes to the redundant indexes.

Removing the redundant (and unnecessary) indexes will not only make
the file smaller, it will also improve performance.

Likewise, it will decrease the amount of fragmentation in the file,
since only necessary indexes will have non-contiguous data pages.
[color=blue]
> . . . However the undocumented and
> sprawling mess of vb code and macros makes it difficult/impossible
> to start whacking it into a proper shape with any certainty of it
> all working when i'm done :( ...so i might just be stuck with
> daily compacting. I'll try removing the excess tables. Then I'l
> re-create the tables in a new mdb and copy the info across -
> anyone have any good code for this so i dont have to re-invent the
> wheel?[/color]

A good search and replace tool like Speed Ferret is likely to make
the job of fixing this application substantially easier. SF used to
be $99, and I save in time the cost of it nearly every time I use
it.

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

"David W. Fenton" wrote
[color=blue]
> A good search and replace tool like
> Speed Ferret is likely to make the
> job of fixing this application substantially
> easier.[/color]

Speed Ferret was the primary commercial tool we used in cleaning up a
client's database prior to "Y2K Remediation". Our client was a small
department in a huge corporation, and were delighted to be reimbursed by
corporate IT for the Y2K Remediation work, including a much-needed cleanup
of their database.

Larry Linson
Microsoft Access MVP

I'm not arguing that that's not true about the indexing, in fact I'd
imagine it can cause very large overhead in cases - but there is very
little indexing in this database... practically none except on primary
keys. In fact if anything I might need to look at actually indexing a
couple of choice fields to speed up things a little!

"BillCo" <coleman.bill@gmail.com> wrote in
news:1120549998.537702.69600@g14g2000cwa.googlegro ups.com:
[color=blue]
> I'm not arguing that that's not true about the indexing, in fact
> I'd imagine it can cause very large overhead in cases - but there
> is very little indexing in this database... practically none
> except on primary keys. In fact if anything I might need to look
> at actually indexing a couple of choice fields to speed up things
> a little![/color]

Have you checked the documenter? Remember, there are hidden indexes
for all joins enforced by referential integrity that are not listed
in table design.

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

"BillCo" <coleman.bill@gmail.com> wrote in message
news:1120549998.537702.69600@g14g2000cwa.googlegro ups.com...[color=blue]
> I'm not arguing that that's not true about the indexing, in fact I'd
> imagine it can cause very large overhead in cases - but there is very
> little indexing in this database... practically none except on primary
> keys. In fact if anything I might need to look at actually indexing a
> couple of choice fields to speed up things a little!
>[/color]

Hi Bill:

I don't know who this Fenton is or why he doesn't understand this problem
but I don't have time to quibble with amateurs. Indexes and table joins do
not cause database bloat. Indexes and table joins have never been
identified by Microsoft as having anything to do with bloat. Go to the
Microsoft Knowledge Base and read all the articles on bloat. You'll see
(1)indexes and table joins are not mentioned (2) then try the proposed
solutions and you'll see they don't work.

Don't butcher your application based on bad advice. Just live with the
bloat by compacting regularly.

Mast