Repost: Database Size Growth

  • Thread starter Thread starter Cory Blythe
  • Start date Start date
C

Cory Blythe

Report from February, still having this expansion issue.

I have recently converted an Access 97 application to
Access 2002, simply put all the application does is read
in a datafile, run calculations, and output a new datafile.

What I have noticed in testing the conversion is that at
the end of the execution the Access 2002 database is
nearly 3 times the size of the Access 97 database running
the same execution.

Once a manual compact is done the Access 2002 database is
much smaller however during execution is is exceeding 2GB
in size depending on the input file compared to 800MB with
the Access 97 version.

My question is if anyone has an explanation for this and
if I missed a setting/conversion issue that I can address
to prevent this.

Thank You
Cory Blythe
 
Most developers will recommend that any temp table processing be done in a
SEPARATE data file that is deleted when you are done with.

(however, the above may not be your case...as perhaps you are just modify
existing data...but if you are creating temp tables...then move that part
out to a temp mdb file. (Tony has some sample code that shows you how to do
this in the below link).


Also, installing the updates to office and JET does fix some bloating
problems also. (so, make sure your office updates and the updates to JET are
up to date.

The best reading on this subject, and the possible fixes can be found at:

http://www.granite.ab.ca/access/bloatfe.htm
 
Cory Blythe said:
Report from February, still having this expansion issue.

I have recently converted an Access 97 application to
Access 2002, simply put all the application does is read
in a datafile, run calculations, and output a new datafile.

What I have noticed in testing the conversion is that at
the end of the execution the Access 2002 database is
nearly 3 times the size of the Access 97 database running
the same execution.

Once a manual compact is done the Access 2002 database is
much smaller however during execution is is exceeding 2GB
in size depending on the input file compared to 800MB with
the Access 97 version.

My question is if anyone has an explanation for this and
if I missed a setting/conversion issue that I can address
to prevent this.

Thank You
Cory Blythe
Click to expand...

In addition to Albert Kallal's suggestions, be aware that the Access
2000+ (Jet 4.0) version of any Access 97 database that contains text
data will always be bigger than the A97 version. That is because the
later versions use unicode to store text, and unicode normally uses two
bytes to represent a single character. That would suggest that a
text-heavy database would be twice as big in Access 2002 as in Access
97, and that's why the database size limit is 2GB for A2k+, as opposed
to 1GB for A97. However, if you set unicode compression on for each
text field, the size difference will be much reduced. So make sure you
have that property set on for your text and memo fields.
 
Back
Top