Re: update 500000 records with adodb.recordset resize the mdb from 140M to 2G

I'm not quite sure whether you misunderstood what I was suggesting, or
whether you did understand, but it is not practical in your specific
situation. The latter is certainly possible, it certainly will not work in
every situation, but just to clarify, what I am suggesting is that instead
of doing something like ...

INSERT INTO tblTarget ( TargetNumber )
SELECT tblSource.SourceNumber
FROM tblSource;

.... followed by something like ...

UPDATE tblTarget SET tblTarget.TargetNumber = [TargetNumber]+1;

.... possibly you may be able to do something like ...

INSERT INTO tblTarget ( TargetNumber )
SELECT [SourceNumber]+1 AS NewNumber
FROM tblSource;

There are a number of KB articles on the subject of Jet database bloat,
here's a link to a search result that lists a bunch of them ...

http://search.microsoft.com/search/results.aspx?view=msdn&st=b&na=82&qu=bloat+access&s=1&swc=3

--
Brendan Reynolds (MVP)


"00090" <706504N@xxxxxxxxxxxxx> wrote in message
news:Xns964375E49A5C60140504NA02619@xxxxxxxxxxxxxxxx
> "Brendan Reynolds" <anonymous at discussions dot microsoft dot com>
> wrote in news:u6ycq3WSFHA.748@xxxxxxxxxxxxxxxxxxxx:
>
>>> 2) I un a VB6 program and load with ADODB the table with 500.000
>>> records.
>> ...
>>> 4) I run an update command with ADODB under VB on each 500.000
>>> records.
>>
>> Why not carry out the modification at the same time as the initial
>> loading?
>>
>
> because original records came from a different source than the
> modifications.
> of course, I can change completly the way I use those datas and keep them
> somewhere in a temporary table before the update.
> but this will not explain why access use 12 times the space instread of
> twice the space. I need to remind that each record is updated
> (recordset.update command) only once.


.