Rocky Smolin
rockysmolin at bchacc.com
Tue Sep 20 13:29:06 CDT 2011
Or you could give them a 'Compact Now' command button on the Main Menu. Or, how about doing nothing? 85MB is not such a big file. Rocky Smolin Beach Access Software 858-259-4334 www.bchacc.com www.e-z-mrp.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin Sent: September 20, 2011 11:21 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Access "Bloat" - Basic Questions Deleting the records and then setting Compact on close will do it unless they keep the app open, or unless the tables are in the back end then I think you have to trigger the compact on the back end through code. Another solution posted in bygone years on the list is to put the temp tables in an external db - create the mdb and tables on the fly, use 'em and delete when done. Rocky -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks Sent: September 20, 2011 10:58 AM To: Access Developers discussion and problem solving Subject: [AccessD] Access "Bloat" - Basic Questions 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