[AccessD] Access "Bloat" - Basic Questions

Wow! You made my day, Brad! If not month, and maybe year! That was the
funniest and most reminiscent email I've had in decades. Thank you for
awaking ugly memories.

On Wed, Sep 21, 2011 at 9:27 AM, Brad Marks <BradM at blackforestltd.com>wrote:

> A big "Thank You" to everyone who shared their ideas on this issue.
>
> I appreciate the assistance.
>
> I do "sort of" miss the good-old-days when I worked in the mainframe
> realm.  The only bloat we ever saw was in the invoices from IBM ... and
> it was huge  :-)
>
> Thanks again,
> Brad
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte
> Foust
> Sent: Tuesday, September 20, 2011 2:27 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Access "Bloat" - Basic Questions
>
> How about writing the data to xml files and deleting the files after the
> query has run?  Any version of Access from 2002 up will handle that
> cleanly
>
> Charlotte Foust
>
> On Tue, Sep 20, 2011 at 10:57 AM, Brad Marks
> <BradM at blackforestltd.com>wrote:
>
> > My background is in the mainframe world where we never had to be
> > concerned about bloat.
> >
> > Now I work in the Microsoft Access world and it appears that I have
> had
> > my first encounter with significant bloating.
> >
> > We have an Access 2007 application that obtains data from several
> > SQL-Server tables (via ODBC) and from two Excel files.
> >
> > This application creates a number of reports.  One of the reports
> needs
> > data from 2 SQL-Server tables and 2 Excel files.  Because of the
> > complexity of the data, we cannot simply use queries to create the
> > report, but we have had to resort to an intermediate Access table that
> > is updated with VBA code via Record-Set processing.  Once the data is
> > processed and stored in this intermediate table, a query is used to
> pull
> > this data from the table for use by the report.
> >
> > The report in question can be run for any desired date range.  If the
> > date range is small, we see little increase in the size of the accdb
> > file.  However, if the date range selected is large (like a full
> year's
> > worth of data), the accdb file grows from 15 MB to 85MB.  I believe
> that
> > this large increase in size is primarily caused by the Access table
> that
> > is used for the intermediate processing of the data.
> >
> > In the mainframe world, we used "temporary work files" for such
> > intermediate processing.  Is there something similar available in
> Access
> > 2007?
> >
> > One idea that I have considered is to use a delete query to clear out
> > all of the records in the intermediate table after the report is
> > generated and then add a "Compact on close" option to the application.
> >
> > Again, I am new to the world of Access bloat and would like to better
> > understand how others handle this issue.
> >
> > Thanks,
> > Brad
> >
> > --
> > 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 message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>