VBA procedure causing mdb size to explode

NHowarth

New member
Local time
Today, 21:22
Joined
May 1, 2013
Messages
8
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:
 
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.
 
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
 
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.:D
 

Users who are viewing this thread

Back
Top Bottom