By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,500 Members | 2,809 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,500 IT Pros & Developers. It's quick & easy.

MDB file bloats while updating records

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
-----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

P: n/a

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.