database getting bigger

[rockyjr]

Just a general question,

I have created an Access 2007 database. There's about 10 users using it. The more they use it, the bigger the database becomes. At first I thought it was normal.... but, I tried a "Save AS" and saved it under another name. At that point the database was 20 mbs. When it finished saving, the database was now 5 mbs.

I checked the database options and I have a check mark in "Compact on Close" and "Remove personal information from file properties on Save".

But that didnt change a thing.

Is there anything else I should check?

Luc

 

[georgedwilkinson]

This is normal behavior. Just compact it every now and then and make sure the database is split.

 

[rockyjr]

I tried to split the database and got the error, "Subcript out of Range". Anyone got this and how can I fix it?

Thanks in advance,

Luc

 

[irish634]

This is normal behavior. Just compact it every now and then and make sure the database is split.

I'm not sure how Access 07 is but I know in previous versions that even if the "compact on close" is checked, it won't actually compact unless you are the only person using the database at the time.

If the database is split, does the "compact on close" actually compact when multiple users are in the database?

 

[georgedwilkinson]

When the database is split, only the front end(s) is(are) ever closed. Since each user should have their own unique instance of the front end, there should never be a problem compacting. Most of the overhead of running SQL is taken on by the front ends so that's where most of your bloat is going to be.

rockyjr, I hope you made backups before you ever even considered splitting. It is not a thing you do lightly.

You may try recompiling a copy of your database (in VBE, Debug|Compile) and see if you can split that without the error.

 

[mvester]

I have an Access 97 application that has been running for years without compacting. It is split and whenever I perform any upgrade or changes, I email the frontend to the client. The backend remains uncompacted. I never found a robust way to automatically relink the tables so I have duplicated the clients environment. Thank goodness for the dos command "subst."

The avoid the bloat on the backend, I recycle the rows that are deleted by the client. One of the columns is for indicating a "deleted" row. In all the queries, the "deleted" column is excluded. When the client appends a new row, I first look for a "deleted" row. If I find one, the row becomes the row with the new data. I avoid doing the two operations that really slow down the performance of a database, appending a new row and deleting an existing row.

Some tables have reached a "steady state" where the number of deleted rows is the same as the number of appended rows. The last time I have done anything with this database is over two years ago. The application has been running for almost 10 years as a MS-Access 97 application. It spent the first 10 years of its life running as a Clipper Summer 87 application using the same method of handling deleting and appending rows.

 

[The_Doc_Man]

Since recordsets are technically unordered (unless you access them via an index, which IS ordered), row-recycling is perfectly OK. As long as you really don't need the implied historical information in the row you are about to recycle.

 

[Pat Hartman]

Technically, if you don't delete records from the back end, there is no space to recover by a compact. However, the compact process also resets the database statistics and that helps to optimize query executions because it helps Jet determine the best way to satisfy a particular query. So, with that in mind, even a slow growing database should be compacted monthly. The more rapidly the back end grows, the more frequently it should be compacted. The front end should always be set to compact on close because certain common practices cause significant bloat and the compact is needed to get rid of the discarded work areas.