Database growing disproportionally

[croydon]

I have just taken on support of a split Access 2003 database. An immediate problem with it is that the back-end grows disproportionally.

There are 14 tables with about 37000 rows in total. The columns are numbers or text, there are no memo fields or stored images. There are no temporary tables or imports. Currently there are 16 users who perform data-entry tasks.

Yesterday the database grew to 140mb and users were being locked out. After compact and repair it reduced to 4mb.

Has anybody encountered this problem? And how can it be resolved?

Any suggestions would be appreciated.

 

[pr2-eugin]

croydon, with multiple users performing read and write there is a possibility of the DB size growth.. If the users are also performing DELETE, without a Compact & Repair the size will only increase..

The only solution is Compact & Repair, in my opinion. I am sure others will have some solution too..

 

[croydon]

Thanks for the reply.

As I understand it, there are some additions and deletions each day but I don't understand why it increases to such a size.

Could it be that the database itself (not the tables) is somehow corrupted and is not being rectified by the compact and repair? Before the database was split, this was an 'all-in-one' application but the forms and other objects were removed leaving just the tables as the back-end.

I was wondering whether creating a blank database, then importing the tables might resolve the problem.

 

[pr2-eugin]

You sure can import to a New empty file.. However, if there are deletions then the problem will be the same..

In Access when a record is created the space for it is reserved and if you delete the record the space remains unclaimed (as garbage) until a Compact & Repair is performed, at which point the operation of Garbage collection along with Fixing issues with the DB are performed..

 

[croydon]

No, the back-end contains only tables now.

I have found out something that may be significant. There is one process where a user opens a spreadsheet and selects the first four columns from row 2 to the last row, then pastes them to the end of a datasheet (on the "*" row). Any duplicates appear in a Paste Errors table in the front-end.

She then runs a query that updates these new records with data from another table.

This morning, after this process, the size jumped from 5MB to 77MB.

Is there any way to see the size of each table in the database? I.e, to determine (from a before and after) which one has grown?