MS Access 2002 Excessive Growth

60 pts.
Tags:
Access 2002
DAO
Microsoft Access
Microsoft Access Performance
Visual Basic
I have an Access 2002 application that is running VB/DAO to manipulate data and have very few forms. When the application starts its size is about 20,000 KB. After completion of a run 4 hours later, it can grow in size to over 200,000 KB. And as the run progresses, it gets slower and slower which I suspect is related to this growth. I close all open recordsets and set the database and recordsets to nothing prior to exiting a procedure. Any insight as to what is happening with this growth or how I can track down the problem?

Answer Wiki

Thanks. We'll let you know when a new response is added.

How many rows are you manipulating. What is it doing? inserts,updates, deletes?

If you are manipulating alot of records – constantly inserting, updating, deleting, the DB grows due to temp space being created to do these things. This is built into Access and it is why there is a Compact feature to get rid of all that temp space.

You may be able to reduce the size by splitting your data table into another Access DB and your application can link to that DB.

Bruce, I think you’re describing a “bloating database”. Try two things: 1) set the default to automatically compact and repair on each exit and, 2) cut back on special formatting,e.g. graphics. If graphics are necessary for instance a logo or picture, place the graphics in a subform.

Good luck!

JuJu

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Rbrucejones
    One of my tables has over 6000 rows and during the run in question only gets updated. No deletes or inserts. Another table I have grows from nothing to over 12,000 rows during the run with no deletions and constant updates. This table as well is constantly being opened and closed. Am I correct in assuming that temp space gets allocated each time this table is opened and not deallocated when closed? Also is temp space allocated when queries like DLookup, DMax, DMin etc are executed and likewise not deallocated? There are several other tables each typically under 100 rows that incur limited inserts and updates. At the end of a run, I do a “Compact and Repair”. Is there a way to programmatically perform a “Compact” from Visual Basic say several times during the run? I will have to try your suggestion on splitting the data table into another Access DB.
    60 pointsBadges:
    report
  • SbElectric
    Access database table with 6000 rows is not a problem but the table that grows from nothing to 12K raises some concern. It sounds like a temporary holding file that gets cleared after processing. I am sure; you and the programmers have looked into alternate design aspects. You may consider adding some additional tables with keys to avoid this build-up of 12,000 rows. Splitting table into another database and even splitting table in the same database with static data and dynamic data may also bring some processing load relief. Unfortunately, Access database can not be compacted during processing – needs exclusive control.
    2,540 pointsBadges:
    report
  • Rbrucejones
    Thanks for all the input. In short, I have had good success. Here's an update. I took my growing table and dropped it into a new data base and linked to it. As a result the new data base did not show any growth during the run as the table grew. I suspect that since the data base was being continually opened and closed it dropped any temporary space as I had not set Compact and Repair to run on closing with this new database. The original database stayed approximately the same size from when it started, then at one point it jumped from 20,000 KB to about 30,0000 KB then stayed there for the remainder of the run. This seems odd so will be checking this out. As far as performance goes, the run is now taking less than half the time it did previously! I have had some thoughts further to my own question of programmatically performing a “Compact and Repair” from Visual Basic say several times during the run, although from what I am seeing this will not be required. Given that the growth table is in the database which is not running any VB code, when it is not being accessed by the application, I found I could open it, view the table and close it. As a result, my plan if required would be to invoke an external process from my application to perform a Compact and Repair making my application wait until the Compact and Repair had completed then continuing.
    60 pointsBadges:
    report
  • Rbrucejones
    Further to this discussion of 3 years ago, I did implement an external process that gets started upon my Access application exiting then which then causes my application to restart and continue processing. That works well. I have however discovered an intriguing situation where I experience very little growth so much so there is no need to perform a Compact and Repair every couple of hours as I used to. If I have another instance of my application open just sitting there while I start up the instance I need to run, I experience very minimal growth in the running instance. Furthermore, the throughput performance which typically degrades with time now seems to remain fairly constant. And if revert back to my normal mode of operation where I run the one instance without another copy sitting there open, it will grow as per normal hence requiring a shut down and restart to reduce the size. I have no idea why this situation occurs but thought it would be useful to get this information out there!
    60 pointsBadges:
    report
  • TomLiotta
    One of my tables has over 6000 rows and during the run in question only gets updated. No deletes or inserts. Just one table of multiple tables? When the application starts its size is about 20,000 KB. No inserts, but the database starts at 20K where one table has 6K rows? What do you store in 6K rows when each row is only 3 bytes long? What would you be updating? There seems to be more to this database that needs to be described. Can you tell us more about what it contains? Tom
    125,585 pointsBadges:
    report
  • Rbrucejones
    The application performs data analysis and contains 8 main tables so the size of the database grows as the results from the data analysis are captured . During processing, 2 of the tables have minor updates only (1 field in each table is updated), 4 of the tables contain approximately 30 fields each and grow from 1 record to about 600 records each and grow through mostly inserts. Another table is a 1 record table containing 132 fields and is just updated with no growth. The final table is a 22 field table that grows from 1 record at the start of a run to about 20,000 records. Current Application Stats (Apr 2012) .mdb – Starting size at the beginning of a data analysis run is 19MB of which 5MB represents VB code and almost 1MB in forms hence leaving 13MB in tables. Once the data has been processed the .mdb grows to about 38MB in total and given that the VB code and forms remain the same size, the data in the tables has increased by 19MB to about 32MB. These statistics are determined after the .mdb has been “Compacted”. However, when I import the data only from another new .mdb instance, the data actually drops to about 20MB from the 32MB which seems reasonable given the growth in the tables due to having run the data analysis. The issue I encountered was that the .mdb grows during processing which can take 8+ hours in total and can easily grow to over 200,000MB. As it grows, the processing through put would get slower and slower to the extent that I would have to stop the processing, shut the application down which includes a Compact and Repair, then resume processing again. Now, with this newest positive wrinkle, if I have an instance of MS ACCESS already open, not processing anything and then I open up my .mdb application and start the processing of my data run, I can do a complete run and only have my application grow by less than 50% hence no need to shut down my application and Compact. My throughput performance remains constant and does not degrade. If I don’t have this other instance of MS ACCESS open, my application will grow necessitating the shutting down of my application, Compacting then restarting.
    60 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following