Home > IT Answers > Database > MS Access 2002 Excessive Growth
Rbrucejones
25 pts.
0
Q:
MS Access 2002 Excessive Growth
Microsoft Access, Access 2002, Visual Basic, DAO, Microsoft Access Performance
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?
ASKED: May 6 2009  3:23 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Juju
335 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
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
Last Answered: May 7 2009  9:21 PM GMT by Juju   335 pts.
Latest Contributors: Randym   1395 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Rbrucejones   25 pts.  |   May 7 2009  4:01PM GMT

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.

 

SbElectric   1575 pts.  |   May 8 2009  2:20AM GMT

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.

 

Rbrucejones   25 pts.  |   May 8 2009  6:38PM GMT

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.

 
0