Updating Records - bloated database

[HedgePig]

I'm having problem with my database size becoming bloated. Here's the story - all of this is being done within VBA.

I have an empty table which I append records to by means of an INSERT query. I really only append skeleton information, enough to have a unique identifier - Customer ID & Transaction ID. All the other fields are null.

Once this is done, I loop through all the records in this target table. I pick up information from another table and update individual fields in the target table based on what I find in the other table. Altogether there are around 70,000 records and perhaps 20 fields in each record that are updated.
This seems to cause the database to bloat in size - and in fact exceeed the 2GB limit. Compacting the database shrinks it back down to a much more reasonable size.

Why would editing individual fields cause the database size to bloat so much? Is it because Access is storing the previous values so the updates could be undone? If so, is there anyway to prevent this?

Of course I'm by no means certain that it is the editing of the fields that is causing this bloat but I have done some testing and this seems to be the cause. I tried populating the fields in the first step (with the INSERT query), and then only editing the ones that needed changes. This produces the same end result but requires far less editing - and the bloat is much smaller, although it's still quite big.

I'd love to hear any suggestions as to what is causing this and how I could avoid it.

In case it's relevant, I'm using Access 2007 under Windows XP.

 

[Derek Brown]

For the benefit of others who may come across this post, the other forum mentioned above is:
http://www.dbforums.com/microsoft-a...a-updating-fields-causing-database-bloat.html

 

[jasmith4]

It seems you're doing a LOT of good coding, but for nothing! Create a make-table query whose SQL is:

SELECT CustomerID
, MAX(Field3) as Field3
, MAX(Field4) as Field4
, MAX(Field5) as Field5
, MAX(Field6) as Field6
, MAX(Field7) as Field7
, MAX(Field8) as Field8
INTO TargetTable
FROM SourceTable
GROUP BY CustomerID;

...and it will re-create your target table every time. If MAX isn't what you're looking for, you can use SUM, COUNT, AVG, MIN, STDEV, VAR, FIRST or LAST.