I have resolved this problem myself but can not understand why there was an issue in the first place and would welcome an explanation.
I have developed an Access ’97 database. Main table (table1) is 150,000 rows. Database size approx 30Mb. I have to manipulate the data into a similar table of 150,000 with some additional fields (table2).
1) Use sql query to build table2 from table1 with additional fields unpopulated.
(so now we have table1 and table2 containing 150,000 rows each)
2) Function that reads a recordset which is a query joining table1 to table2 on unique key.
Looping round the whole recordset,
4) Populate additional fields which are all in table2
In Access ’97 everything performs as expected, the database grows from 30Mb to 41Mb and compacts back to approx 30Mb
When step 4 populates just one text field of 1 char length, in Access ’97 I have approx the same growth. However with Access 2000, the database grows from 30Mb to a monster 487Mb but only when a text field is changed in the update. From 487Mb the Access 2000 database compacts down to it’s expected size of 30Mb. Why the abnormal growth only when it’s a text field being changed? It does not behave like this with a numeric field?
I have changed the Technique such that table2 starts empty and grows one record at a time within the function, replacing the Edit/Update method with Addnew/Update:
1) Function that reads a recordset from table1 and writes a second recordset which is table2
Looping round recordset1
3) Populate all fields in Recordset2 including additional fields
Now my Access 2000 database grows from 30Mb to just 33Mb and everything is just fine.
I have never come across anything like this, and would be grateful for a eureka moment if somebody could explain why this was happening. I have experimented with indexes and with no indexes but always the same results.