MDB file bloats while updating records

[Alexey.Murin]

The application we are developing uses MS Access 2003 database (with
help of ADO).
We have noticed that during massive records updating the size of the
mdb file increases dramatically (from 3-4 to 200-300 Mb).
After compacting the file shrinks back to 3-4 Mb.

I have performed the following experiment. I created a test database
containing only one table with two columns (Key number, Value
text(50)).
The table contains 10 thousands records.

I have run two slightly different functions to update the table.
After running of Function 1 the mdb size is not changed significantly
(about 5-6%).
BUT if I run Function 2, database bloats from 0,5Mb to 39Mb !!!

Does anyone know why this happen?
Is it normal behavior of bynamic recordsets?

Thanks in advance,
Alexey

Function 1:
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Table1", dbOpenDynaset)
While Not rs.EOF
db.Execute ("Update table1 set [value]='value1' where [Key] = "
& rs![Key])
rs.MoveNext
Wend

Function 2:
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Table1", dbOpenDynaset)
While Not rs.EOF
rs.Edit
rs!Value = "value1"
rs.Update
rs.MoveNext
Wend

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Bloat in Access is pretty common. You might try changing your method of
updating the table. Instead of individually updating each row in the
table using a recordset you can use just one SQL qry:

db.Execute "UPDATE Table1 SET [Value] = 'value1'", dbFailOnError

This will do the same thing you've got in Function 1 & Function 2; i.e.,
update all the rows in the table.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ8wIhIechKqOuFEgEQKoJgCfdYcDyWZxQJtjy9OdSAH0wp +ElhMAnRod
f4tr3+cjnHy8RH4Club3tRfE
=6atx
-----END PGP SIGNATURE-----
Al**********@gmail.com wrote:

The application we are developing uses MS Access 2003 database (with
help of ADO).
We have noticed that during massive records updating the size of the
mdb file increases dramatically (from 3-4 to 200-300 Mb).
After compacting the file shrinks back to 3-4 Mb.

I have performed the following experiment. I created a test database
containing only one table with two columns (Key number, Value
text(50)).
The table contains 10 thousands records.

I have run two slightly different functions to update the table.
After running of Function 1 the mdb size is not changed significantly
(about 5-6%).
BUT if I run Function 2, database bloats from 0,5Mb to 39Mb !!!

Does anyone know why this happen?
Is it normal behavior of bynamic recordsets?

Thanks in advance,
Alexey

Function 1:
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Table1", dbOpenDynaset)
While Not rs.EOF
db.Execute ("Update table1 set [value]='value1' where [Key] = "
& rs![Key])
rs.MoveNext
Wend

Function 2:
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Table1", dbOpenDynaset)
While Not rs.EOF
rs.Edit
rs!Value = "value1"
rs.Update
rs.MoveNext
Wend

[Alexey.Murin]

I understand that just one query is enough to update all row with the
same value. But in the real application each record receives its own
individual value.
Thats why I update each record individually in my test database.

Thank you,
Alexey