[AccessD] Back end bloat

McGillivray, Don [IT] Donald.A.McGillivray at sprint.com
Mon Dec 15 19:51:41 CST 2008


Hi, Asger

What you suggest is similar to what I'm doing (see my post in response to Charlotte), and it avoids those instances where Access woun't allow an action query to run if it has any components based on a non-updatable data source (such as a linked text file).  Actually, I do use that technique in my app in other places.

Thanks for the response!

Don

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond
Sent: Monday, December 15, 2008 4:13 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Back end bloat

Hi Don,

In your first posting, I read:
"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."

Is it viable for you to 1) have your app store the data in a text-file
(overwriting said text-file as needed), and 2) have your BE attach the
text-file?
Attaching your BE to a text-file will not cause any mdb-bloating, no matter
how often the text-file is renewed.
Just a thought...

Asger

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af Darryl Collins
Sendt: 15. december 2008 23:55
Til: 'Access Developers discussion and problem solving'
Emne: Re: [AccessD] Back end bloat


Hi Don,

I have no where near the skills and experience of many folks on this list
(at least with Access / SQL Server), however I recall reading (and have
experienced) issues with recordsets not being released correctly.  It might
be worthwhile checking your code to check that in addtion to closing the RS,
you also set it to nothing.  Closing it does mean you have released it -
this maybe adding to the bloat.  I have found it worthwhile specifically
setting each one to nothing when done with it (along with anything thingelse
that has been set such as cnn or db or...).


rs.close
set rs = nothing

might be worth a test at least.

hth a bit.

Cheers
Darryl.


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of McGillivray,
Don [IT]
Sent: Tuesday, 16 December 2008 4: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

This email and any attachments may contain privileged and confidential
information
and are intended for the named addressee only. If you have received this
e-mail in
error, please notify the sender and delete this e-mail immediately. Any
confidentiality, privilege or copyright is not waived or lost because this
e-mail
has been sent to you in error. It is your responsibility to check this
e-mail and
any attachments for viruses.  No warranty is made that this material is free
from
computer virus or any other defect or error.  Any loss/damage incurred by
using this
material is not the sender's responsibility.  The sender's entire liability
will be
limited to resupplying the material.


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



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


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.





More information about the AccessD mailing list