MDB Size increases rapidly

[Ed Kopta]

I have an Access 2002 mdb that increases in size about 6-12MB when I use a
particular form that automates a data-entry operation. Highlights of this
operation are:

1) Create two temporary tables (one a subtable of the other) by using VBA to
copy tabledefs
2) Get data by opening Excel in the background and cycling through targeted
cells
3) Using action queries to fill data from temp tables to original ones, and
also to fill other tables with new data (these other tables hold text values
from the worksheet that are related to the original tables by primary key
values).

I understand that I can always compact very frequently or code a menu
command to do so, but I was wondering if there are any other general
principles involved that would allow me to address this issue more
elegantly. Maybe there are some classic no-nos involved with the above
operations. Has anyone dealt with problems like this?

Thanks for any help you can give
-Ed Kopta

 

[Marshall Barton]

I have an Access 2002 mdb that increases in size about 6-12MB when I use a
particular form that automates a data-entry operation. Highlights of this
operation are:

1) Create two temporary tables (one a subtable of the other) by using VBA to
copy tabledefs
2) Get data by opening Excel in the background and cycling through targeted
cells
3) Using action queries to fill data from temp tables to original ones, and
also to fill other tables with new data (these other tables hold text values
from the worksheet that are related to the original tables by primary key
values).

I understand that I can always compact very frequently or code a menu
command to do so, but I was wondering if there are any other general
principles involved that would allow me to address this issue more
elegantly. Maybe there are some classic no-nos involved with the above
operations. Has anyone dealt with problems like this?

Creating tables and deleting/adding records is a major cause
of bloat. This kind of operation should be avoided if at
all possible. Unfortunately, Import operations often do
benefit from th use of temp tables.

If you can not find a reasonable way to avoid them, then you
should place them in a temporary database. Here's a good
way to manage the ins and outs of all this:
http://www.granite.ab.ca/access/temptables.htm

 

[Ed Kopta]

That link looks promising. Thanks for the help!

-Ed Kopta

 

[David C. Holley]

You may also want to consider procedures. Do you have to import the data
hourly or will daily do?