McGillivray, Don [IT]
Donald.A.McGillivray at sprint.com
Thu Jan 1 11:37:59 CST 2009
Thanks, Dan.
That's certainly worth a look-see. I live in a tightly controlled corporate environment, where all installations are governed by "approved policies". The powers that be grudgingly allowed Access to be installed because of this and several other legacy apps that we use. I'll do the research, and if I find that we're out of date I'll request an update. Wish me luck.
Don
-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: Thursday, January 01, 2009 8:53 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Back End Bloat - Revisited
Hi Don,
As a guess - you might investigate to see if you are using the latest
version of Jet. You'll have to research it - the latest version depends on
which operating system/version is running the server.
Dan
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of McGillivray, Don
[IT]
Sent: Thursday, January 01, 2009 10:35 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Back End Bloat - Revisited
Thanks for responding Edward.
In my attempts to stabilize this thing, I have already de/re-compiled and
compact/repaired the front end. I also went thru every procedure in the
front end that uses a DAO recordset and verified that they are all closed
and set to nothing. For the back end, I imported all the tables into a new
db container. I also turned off all the auto-correct stuff, and removed all
the sub-datasheets. The back end has no code in it, so there's nothing to
re-compile. This system has been fairly stable for several years, requiring
only occasional compaction of the back end to keep it humming along.
Suddenly, it won't last 12 hours without growing to 2 GB. It's making me
crazy to have to hand hold it around the clock.
I'll check the links you provided to see if there's anything there that
helps.
Don
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Edward Zuris
Sent: Wednesday, December 31, 2008 7:49 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Back End Bloat - Revisited
I have had good luck with Decompile to srink Bloat from
time to time.
Charlotte Foust warns us about some hidden system objects
issues with Decompile. So keep an eye out for that.
Here is what I did.
With the example below, a 50 megabyte MDB file went down to
ten megabytes and it became a little more snapper.
1). From the run command I issued the fillowing line.
C:\OfficeW2k\Office\msaccess.exe /decompile
"C:\KateUpload\zEmp027c2.mdb"
2). After the decompile was finished I opened up the Access
MDB file, went to modules. Open one of them modules in
design mode do a Compile, then File on the menu line and
save everything.
The example went from 50 down to 10 megabytes, which is
a big improvement.
Please are there are warning not to over used the decompile switch.
For more information please look at the following web sites.
http://www.mvps.org/access/bugs/bugs0008.htm
http://www.trigeminal.com/
http://www.trigeminal.com/michka.asp?1033
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of McGillivray,
Don [IT]
Sent: Wednesday, December 31, 2008 7:05 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Back End Bloat - Revisited
Hello All
A couple of weeks ago I posted a question concerning a bloating problem
in the back end database used by my application(s). Thanks again to all
of you who took the time to respond. I implemented all the solutions
that were suggested, but still the problem persists. So, here I am
again with hat in hand . . .
The problem in a nutshell:
The back end database contains nothing but tables - no queries, no
forms, no reports, no code of any kind.
With a very few minor exceptions (~150-200 8-field records/month) data
is only added to the tables - not removed.
There are two applications that access the back end around the clock -
one gathers data from other sources and populates the system tables, and
the other provides a user interface which retrieves the data via reports
and various forms bound to the system data.
Most data is added to the tables by means of either saved insert queries
or SQL built on the fly and executed using "db.execute" statements in my
code. All such queries/code reside in the front end application mdb.
I have examined my code thoroughly, and can verify that there are no DAO
recordset objects created in the front end that are not subsequently set
to nothing, the setting to nothing occurring always in a post error
handler exit routine embedded in each procedure. All DAO recordsets are
being closed before being set to nothing.
The BACK END is where I'm having the problem - NOT the front end.
(There is some insignificant growth in the FE, but it's the BE that is
off the charts.)
The app can run for several complete processing cycles (each cycle
involves the collection of data from text files produced by other
systems) with no weight gain - NONE, and then it will suddenly leap in
size by as much as 100 MB. Within a couple of hours, it will have grown
from about 150 MB to well over 500 MB. Then within 24 hours, it will
have grown to a size approaching 2 GB, requiring a compact. The last
time I ran through this routine, the compacted size was just 8K larger
than the size it started out at before it started growing.
I'm getting desperate here. Still hoping that somebody can suggest an
approach that will stop the madness.
Many 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
--
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.
--
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.