467,894 Members | 1,586 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,894 developers. It's quick & easy.

MDB file bloats while updating records

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

Jan 16 '06 #1
  • viewed: 2510
Share:
2 Replies
-----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

Jan 16 '06 #2

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

Jan 17 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Chris Jackson | last post: by
4 posts views Thread by Darrel | last post: by
1 post views Thread by davidgordon | last post: by
7 posts views Thread by M | last post: by
reply views Thread by bigove | last post: by
10 posts views Thread by chimambo | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.