Access file became very large during updating

[pggsb]

I am writing a vba procedure to updating some records in another Access database.

rsAccess.Open "SELECT * FROM AI_Table",conAccess, adOpenForwardOnly, adLockPessimistic

rsAccess!OCRExist = "Exist"
rsAccess.Update

it has about 3 millions of records in that AI_Table. In the procedure, I perform some calculation and put the result into a TEXT(50) field in the AI_TABLE. As it was updating the records, I could see the size of the Access database file (the one contained AI_Table) grew very quickly, almost 1 MB/sec. I am pretty sure I am not adding that much data. If I stop the procedure and packed the database, it shrunk a lot.

I am just wondering if there is anything wrong with the way I am locking or updating the records.

Thanks,
pggsB

 

[llkhoutx]

You are adding a lot of data.

Three million records is a lot to ask of Access. You done say what kind of table it is, Access, Oracle, SQL Server, etc. or whether it's in a backend database.

I assume that you know that Access mdbs are limited the 2 G-bytes in size. Split backends excluded.

If you don't know what you're doing you are going to get your lunch eaten.

 

[pggsb]

The table is in an Access file. The Access file is now 400MB and the table has a few fields it it. When I just tried to update one field with a single character "Y" through VBA script, it Access file size will increase dramatically and crashed after it reached 2GB limit (when it updated about 200 thousands records) . If just run a query in Access to udpate those 200 thousands, the file size only increased by a few MB.

So my question is why Access file size increate so much while I update the record through VBA script using ADOBD update method.

Thanks again!

Pggsb