Database growing disproportionally

croydon

Registered User.
Local time
Today, 21:10
Joined
Oct 12, 2006
Messages
22
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.
 
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..
 
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.
 
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..
 
Is there anything in the BE besides permanent tables?

Typically bloat is caused by objects being modified. When I am actively developing, the FE grows larger and larger until I compact it. BE's typically don't bloat because objects are not being modified. So there must be some append query that the users run and perhaps cancel before completion. So if the query tries to append 500 rows and the user cancels, the space needed to hold those 500 rows is already allocated and cannot be recovered or reused. Only a cr will get rid of it. What about delete queries? Do you use querydefs or SQL strings in VBA. If you use querydefs it is really easy to find the action queries and then search the code to find where they are run.
 
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?
 
It is quite likely that the spreadsheet contains thousands of "empty" rows and those are causing paste errors. They still occupy space because Access has to plan for them and only later learns that they are invalid.

Most people don't understand how to actually delete data from Excel. If you just select a cell and press the delete button, you clear the cell contents but you don't actually delete it. Same for if you select a row or a column. The delete button clears the contents but the row/column still occupy space and that is normal for a spreadsheet. People frequently insert empty rows to enhance the presentation. Excel can't tell the difference. To delete a row, select it and choose the delete option from the ribbon.

To get around this if you don't have any control over the spreadsheet, link to it instead of importing it. Then use an append query that selects only the non-null rows.
 

Users who are viewing this thread

Back
Top Bottom