MDB Size increases rapidly

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ed Kopta - 03 Feb 2005 18:02 GMT

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

Reply to this Message

Marshall Barton - 03 Feb 2005 19:24 GMT

>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
[quoted text clipped - 14 lines]
>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

Signature

Marsh
MVP [MS Access]

Reply to this Message

Ed Kopta - 03 Feb 2005 20:27 GMT

That link looks promising. Thanks for the help!

-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
[quoted text clipped - 28 lines]
> way to manage the ins and outs of all this:
> http://www.granite.ab.ca/access/temptables.htm

Reply to this Message

David C. Holley - 04 Feb 2005 05:20 GMT

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

>>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
[quoted text clipped - 26 lines]
> way to manage the ins and outs of all this:
> http://www.granite.ab.ca/access/temptables.htm

Reply to this Message