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

Tech-Archive recommends: Fix windows errors by optimizing your registry

> 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?

--
Brendan Reynolds (MVP)

"00090" <706504N@xxxxxxxxxxxxx> wrote in message
news:Xns9642E62EA14FD0140504NA02619@xxxxxxxxxxxxxxxx
> "William \(Bill\) Vaughn" <billvaRemoveThis@xxxxxxxxxx> wrote in
> news:##PpvI8RFHA.2788@xxxxxxxxxxxxxxxxxxxx:
>
>> Each UPDATE causes a delete and insert. If you update every row, you
>> double the size of the database (at least). The upper limit of a JET
>> database is 2GB. It sounds like you're trying to carry a ton of coal
>> in Tupperware.
>>
>
>
> thanks for the answer
> I understand this but as I wrote in another message :
>
> 1) I create a new database with 1 table , the Mdb file size is about 256K
> 2) I un a VB6 program and load with ADODB the table with 500.000 records.
>
> 3) I use the compact commands within access : the mdb size is about 150
> Mega
>
> 4) I run an update command with ADODB under VB on each 500.000 records.
> the
> program stop with the 2G limit error and the Mdb size is 2.04 Giga
> 5) I compact the database again and the mdb is about 150 mega
>
> conclusion : updating each 500.000 record produce a wasted space of about
> 12 times the initial table space. and THIS is the problem.
>
> normally, update a record can produce the fact that access need to move
> the record somewhere else in the tablespace. such behaviour is common
> with other database especially if the modifications made on the record
> need more textual space. that is updating 500.000 records with produce 2
> x 500.000 record space. NOT 12 x 500.000


.

Relevant Pages

  • Re: Simple question? Updating database...
    ... > I'm having trouble with updating my database which is SqlServer. ... > the update command ... > and only the dataset is being changed not the actual row in the database. ...
    (microsoft.public.dotnet.general)
  • Simple question? Updating database...
    ... I'm having trouble with updating my database which is SqlServer. ... the update command ...
    (microsoft.public.dotnet.general)
  • Re: VBA CODE - ADODB
    ... i have been looking at vb6 and ado and dao. ... i have looked at the coding for adodb - and it does ... > Dim dbShop As Database 'declare database object ... > Dim rsShop As Recordset 'declare recordset object ...
    (microsoft.public.vb.general.discussion)
  • RE: problem getting data set to update
    ... whenever I called the Update method on the data adapter. ... > Manually selecting values out of the dataset does reflect edits to the text ... but running the Update command does NOT update the database. ...
    (microsoft.public.vb.database)
  • QueryInterface failed - InvalidCastException
    ... We have a Data Integration application which is working in a lot of sites. ... The program is doing some integration tasks between our database and another software company database which is called XXX. ... We have a strong suspicion about ADODB dll, because the com object uses 2.5.0.0 version of adodb and error description has ?System.InvalidCastException: ... So I have downloaded csrepair.exe for repairing the adodb registery. ...
    (microsoft.public.dotnet.framework.interop)