VBA procedure causing mdb size to explode

[NHowarth]

I have a fairly simple procedure (using recordsets) that is editing one field in one table, on each of a million or so records. Don't get upset by the number of records, the database works fine, multi-user, with all those records. The single field is a Number, size Long (4 bytes), and when I add the number in using the VBA as follows:

>
FileSet.Edit
FileSet!AirframeID = AcID
FileSet.Update

>

where AirframeID is the Long-Number field and AcID a LONG variable, what happens is that after about 360,000 records the database has grown from 650mb to the 2Gb limit. I then get the Access 3049 error.

So why, when updating a 4 byte-field on 360,000 records does the mdb size grow so dramatically ? I expected the whole million records would only add 4mb to the size. This one's a real puzzle to me. Appreciate help from those more knowledgeable.:banghead:

 

[mdlueck]

This is the nature of the beast... Access files bloat up with use, and require compaction.

A thorough clean up of the Access DB file is as follows:

NT Command Script and Documented Steps to Decompile / Compact / Compile an Access DB
http://www.access-programmers.co.uk...to_Decompile_/_Compact_/_Compile_an_Access_DB

 

[Pat Hartman]

Where is AcID coming from? It is almost always more efficient to run an action query to update records than to use a code loop.

 

[NHowarth]

Hi Pat
AcID is just a variable long integer that starts at 1 and increments by 1 in each loop iteration, and by the time it gets to the 360,000th record, it's at 360,000. I will try your suggestion of an update query and see if the database doesn't expand dramatically as it has been doing. There just seems no logical reason why updating a 4-byte field a million times would add anything more than 4mb to the size. How it increases from 650mb to 2gb is beyond me !
Anyway, I'll try the update method and report back.
Thanks

 

[NHowarth]

Success ! on Pat's suggestion I ran almost a million update queries, rather than using Recordet.Edit statements, and it worked perfectly. The database increased in size only by about 7mb, which is just fine. No unexpected massive file-size growth. Thanks again.