Jim Dettman
jimdettman at verizon.net
Thu Jan 1 12:32:01 CST 2009
Don, Do you have more then one process updating/appending records? Jim. -----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 1:06 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Back End Bloat - Revisited Jim, It's a little of both - some updating and some appending, and almost no deletion. Running Access 2003 (SP3), with Jet 4.0. Using the Access 2000 file format, and it's running in a terminal session hosted on a Windows 2003 server. Thanks! Don -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman Sent: Thursday, January 01, 2009 9:35 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Back End Bloat - Revisited Don, <<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.>> Are you updating existing records or just appending? And what version of Access/JET is this? Jim. -----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 9: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