[?] mdb unexpected file growth

[Moreno]

Hi,
I've an Access 2002 db, with only one table containing about 400.000
records.
I've to make an update on every record, and I've created a little VB program
to do it. ( VB 6, ADO 2.5 )
The mdb file at start is about 50 Mb, but when the update process is
terminated it's about 1,4 Gb !!!!

Is there any way to avoid this file growth ?

Thanks.

Moreno

 

[John Vinson]

Hi,
I've an Access 2002 db, with only one table containing about 400.000
records.
I've to make an update on every record, and I've created a little VB program
to do it. ( VB 6, ADO 2.5 )
The mdb file at start is about 50 Mb, but when the update process is
terminated it's about 1,4 Gb !!!!

Is there any way to avoid this file growth ?

Compact the database after running the update.

Better... use an Update query rather than looping through a recordset.
Much faster and more efficient. You'll still need to Compact however.

 

[Moreno]

There isn't any primary/unique key on that table and I'm not sure an UPDATE
statement would modify only the current row.

I should add a Counter column and make it the primary key, change the
program to issue an update statement on the primary key , run the program
and finally drop the added column........

OK, I can do it, but can you explain me why looping through recordset and
changing a column value causes such an increment in the file size while an
"UPDATE table set column = 'xxxxxxxxxxx' " statement doesn't ?

Thanks

 

[John Vinson]

There isn't any primary/unique key on that table

Every table should have a unique key; without it there is no way (in a
query or in code) to determine uniquely which row you want to update.

and I'm not sure an UPDATE
statement would modify only the current row.

It would if it contains criteria which identify the row to be updated.
Access would be pretty near useless if it couldn't! If you can
identify the row in your VB code, you can identify the row in a Query.

I should add a Counter column and make it the primary key, change the
program to issue an update statement on the primary key , run the program
and finally drop the added column........

Note that a Counter is NOT NECESSARY. For one thing, if you're
updating a single record, you simply need a query selecting that
record; if the combination of fields which identify the record are
indexed the query will run faster (again, either in code or as a
query), but it'll update the right record either way.

OK, I can do it, but can you explain me why looping through recordset and
changing a column value causes such an increment in the file size while an
"UPDATE table set column = 'xxxxxxxxxxx' " statement doesn't ?

Well, it does increase the file size, and if I suggested that it
doesn't, I apologise. Access doesn't garbage collect after any event
which changes the contents of a table. That's why I said that you'll
need to compact either way. The advantage of the query is that it's
simpler to program and will generally run much faster, if you have
appropriate indexes.