[AccessD] Back end bloat

So the queries are being created in the front end and executed against
the back end?  That shouldn't cause back end bloat, but if you're
running queries that actually execute in the back end, then yes it will
bloat the database. I'm not sure what you mean about adding and removing
data in a temporary backend.  You still have to pass those changes to
the backend and any changes against backend data will cause at least
some bloat.  The later version of Access create their own hidden queries
for things and that's where a lot of bloat comes from as well.  Stored
queries are generally more efficient in Access because they can be
optimized by the query engine while on the fly queries take a hit there.

Charlotte Foust

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of McGillivray,
Don [IT]
Sent: Monday, December 15, 2008 9:26 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Back end bloat

Hello Folks

I don't know if this is an unexpected problem or not, but the back end
DB for an application of mine has been bloating big time, and rather
rapidly.  It's Access 2003 running on a Win 2000 server, served to my
users via Windows remote desktop.

So far, I've just been dealing with it by compacting the backend now and
then, but I'd like to understand what is going on so that I might fix
the underlying problem.  When compacted this thing is around 150 Mb, and
when it grows to 1.5 Gb or so, I compact it again.  Sometimes it bloats
to 1.5 Gb within a day or two, and sometimes it takes 2 or 3 weeks.
When this app needs to load and remove data, it's done with tables in a
temporary back end that is killed and recreated from a template as
needed.  In the back end, all but two of the tables only get data added
to them, and the two that sometimes have records removed do so at a rate
of < 200 records a week for both tables combined.  These tables have
about 8 fields each.

Don't know whether it makes a difference, but the app uses very few
stored queries to perform its data additions.  Most of of the records
are added using SQL built on the fly and executed using db.Execute.
Also, I've read elsewhere that failure to close recordsets and kill
object variables can cause a DB to bloat, but it wasn't clear from the
description whether that would be expected in a back end or the front
end.  (There's no code in the back end.)  I've been very careful about
killing object variables in my code, but less so about closing
recordsets before killing them.  Before I go looking for every instance
of a recorset not being closed, can anybody help me to understand
whether that may be part of the problem?

Any leads toward the solution will be greatly apprreciated.

Thanks!

Don McGillivray

This e-mail may contain Sprint Nextel Company proprietary information
intended for the sole use of the recipient(s). Any use by others is
prohibited. If you are not the intended recipient, please contact the
sender and delete all copies of the message.


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com