What makes my DB Grow?

[Ross]

I run an Access 97 program which I developed and I have
no problems. I convert it to XP Version 2002 and import
about 370,000 records. The size of the database will
grow to 2,097,152 mg and the database will destroy
(corrupt)itself.

Record pull is a two step process. If I compact after
step 1, I can complete step 2 without a crash but it
still grows exponentially! What is happening?

Thanks

Ross

 

[Albert D. Kallal]

If you are doing just a straight import, and no "processing", or editing of
the records during that import, then I don't see why you are having
problems.

Due to great big thing call the world, all new software from Microsoft
supports what we call Uni-code. That means that appcltions can function all
over the world in different languages (all office products support different
languages now). I suppose the company could be arrogant...and assume the
rest of the world only uses only English...but that is just not practical
today.

As a result, every character stored in office applications is now actually
two characters. However, access xp supports double the file size of access
97. Further, there is some compression technology also included. Thus, as a
general rule..the file will be larger in access xp then a97..but the size
difference is usually manageable. And, with compression...you can often
store more data in a accessXP then access97.

However, there is a number of "bloat" bugs and problems that are fixed by
updates to JET. I would make sure you install the updates to both
office...and ALSO the updates to JET.

You can read about some other fixes...and issues for bloating here:

http://www.granite.ab.ca/access/bloatfe.htm

 

[Ross]

Albert,

Thank you!! I now have some direction with this thing.

Ross

 

[Tony Toews]

I run an Access 97 program which I developed and I have
no problems. I convert it to XP Version 2002 and import
about 370,000 records. The size of the database will
grow to 2,097,152 mg and the database will destroy
(corrupt)itself.

This is an interesting question that comes up occasionally. I'd
really appreciate some feedback on what approach if any helps.

MS changed some things in Jet from 3.5 to 4.0. It seems, and I do
emphasize the word seems, that when doing some imports that Jet now
places each record in it's own 4k Jet page rather than aggregating
multiple records on a Jet page. And 4k x 370k records is about 1.5
gb. If some of those records had memo fields, which require it's own
page, then that could explain the overage.

One interesting thing to try would be to leave the backend in A97/Jet
3.5 and see if the same thing happens. Are you at Jet 4.0 SP6, SP8
or newer?

What method are you using to do the import? APPEND queries, DAO, ADO?

If using DAO/ADO can you change the recordset properties to be
appendonly? If using ADO can you try DAO? Can you open the MDB
exclusive access only so Jet knows that no one else will be trying to
update the MDB.

If using APPEND queries try the dbDenyWrite option if using
myDB.execute.

Tony


--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm