fast increasing database with only tables

[Hans Mol]

I made an application in msaccess97 and converted this to access2000, this is
the front end.
The backend is still an access97 database (this is because I found some
indexing problems in an access2000 database and this seems to be working
perfect)
The database is in use by approximately 25 persons at the same time for
about 1.2 year now.

The size of the database stayed about the same for a period of 1 year.
But recently the backend grows very fast (from 200MB to 1000Mb in one week
time) every time it is compressed, its size is reduced again.

To find the reason for this I compacted the database wiht winrar en found
that during compaction, the progressbar moves on but the compacted file does
not increase in size, this gives me a hint that there is a lot of empty space
in the database.
At the end the size of the compacted file is 25 Mb.

Then I deleted all the tabels in the database (there are no modules,
queries, forms or whatsoever in this database) and did the same again.
To my surprise the size of the compacted (rar) file still was the same and
the size of the original database still was 1000MB.

It looks like the source of the problem comes from 2 tables with a lot of
empty records, these tables are updated frequently and approximately 30 new
records are addes each day.

 

[Dennis]

Working as designed. When you do "work" in Access, workspace is allocated for
whatever task you're doing. This workspace is NEVER RELEASED, and you have to
do a Compact to get rid of it.

Note that if your application filesize ever hits 2 Gb, your application will
die, and you'll probably have to restore from a backup.

 

[Albert D. Kallal]

I made an application in msaccess97 and converted this to access2000, this
is
the front end.

Access 2000 had a LOT of bugs and problems when first released. You REALLY
must ensure that you install all of the service packs to both ms-access and
also JET.

Note that some bloating problems where fixed in these patches.

The backend is still an access97 database (this is because I found some
indexing problems in an access2000 database and this seems to be working
perfect)

Well, it not a huge problem, but I as a general rule ALWAYS run the database
as the SAME version of the product I using, it kind of makes sense...don't
it???

The size of the database stayed about the same for a period of 1 year.
But recently the backend grows very fast (from 200MB to 1000Mb in one week
time) every time it is compressed, its size is reduced again.

I would suggest that you turn off row locking, and also address any code
that uses "temp" tables. (eg: writes records out for reports, and then
empties the table afterwards...this type of code will cause bloat). Another
approach is to use external local files for temp data. Note that row locking
is a NEW feature for a2000, and it possile that it don't work in a97, but I
would disalbe this feature, as it actually "functions" by padding reocrds to
2000 charaters, and is a HUGE source of bloat.

Then I deleted all the tabels in the database (there are no modules,
queries, forms or whatsoever in this database) and did the same again.
To my surprise the size of the compacted (rar) file still was the same and
the size of the original database still was 1000MB.

Compacted rar file, or the process of compacting a database is GRAND CANYON
of diffent issues here. If you encrypted the database, your find it don't
compress using winzip/rar etc much at a all. However, compression is a
RATHER different issue then compact and repair with returns that un-used
space that access tends to build up (especially with code or designs that
uses temp data tables). Compact and repair in ms-access HAS NOTHING to do
with compression technologies. Don't confuse these two VERY VERY seperate
issues.

It looks like the source of the problem comes from 2 tables with a lot of
empty records, these tables are updated frequently and approximately 30
new
records are addes each day.

Updating should not be too much of a problem, but right after you
compact/repair, any update activity will case the file to bloat very
quickly, and then should settle down to a very small growth rate.

As mentioned, you need to install all of the service packs for access 2000,
as it had a lot fixes issued, and some were fixes for bloat. The same
applies for jet....

 

[Hans Mol]

thanks for the reply, the method of row locking can indead cause a problem,
but is also often required if the application is used by a lot of people.
In the mean time I found that not always closing the recordset and the
database can cause sizing problems.
Using a different database than the application is the power of the
application, you must be able to connect anything to it, from oracle
databases to sql.
temp tables are already used in the front end, in the backend only items are
queried through recordsets and some are added and some are modified, but not
that much.
only in very few occasions bulk data is added to a table (150.000 records)
and bulk data is update (150.000 records) but this happens only once a year.
All the recordsets are now examined to see if it is possible to make them
read only and to see if they are closed again.

Might the method of searching be a problem : by means of a recordsetclone.

 

[Albert D. Kallal]

Might the method of searching be a problem : by means of a recordsetclone.

I don't belive so...no.....