Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • Last Modified:

My Access 2000 database has overactive growth hormones, why?

I have resolved this problem myself but can not understand why there was an issue in the first place and would welcome an explanation.

I have developed an Access ’97 database. Main table (table1) is 150,000 rows. Database size approx 30Mb. I have to manipulate the data into a similar table of 150,000 with some additional fields (table2).

Technique (simplified):
1) Use sql query to build table2 from table1 with additional fields unpopulated.
   (so now we have table1 and table2 containing 150,000 rows each)
2) Function that reads a recordset which is a query joining table1 to table2 on unique key.
Looping round the whole recordset,
   3) Recordset.edit
   4) Populate additional fields which are all in table2
   5) Recordset.update

In Access ’97 everything performs as expected, the database grows from 30Mb to 41Mb and compacts back to approx 30Mb

When step 4 populates just one text field of 1 char length, in Access ’97 I have approx the same growth. However with Access 2000, the database grows from 30Mb to a monster 487Mb but only when a text field is changed in the update. From 487Mb the Access 2000 database compacts down to it’s expected size of 30Mb. Why the abnormal growth only when it’s a text field being changed? It does not behave like this with a numeric field?

I have changed the Technique such that table2 starts empty and grows one record at a time within the function, replacing the Edit/Update method with Addnew/Update:
1) Function that reads a recordset from table1 and writes a second recordset which is table2
Looping round recordset1
   2) Recordset2.Addnew
   3) Populate all fields in Recordset2 including additional fields
   4) Recordset2.update

Now my Access 2000 database grows from 30Mb to just 33Mb and everything is just fine.

I have never come across anything like this, and would be grateful for a eureka moment if somebody could explain why this was happening. I have experimented with indexes and with no indexes but always the same results.

Many thanks.

Jeremy


0
JHVivian
Asked:
JHVivian
  • 6
  • 5
  • 4
  • +2
1 Solution
 
John Mc HaleForensic Computer Examiner, Analyst/Programmer & Database ArchitectCommented:
There have been numerous discussions in the past, which tend to point in the direction that if you are converting a 97'database to v2000, then you should consider creating a new MS Access 2000 database first. Then, after having first compacted/repaired the '97 database, import all the required objects into the v2000 database before finally opening up the module window and re-compiling for v2000, followed by a compact/repair cycle. Although I have seen occasional 'Bloating' of an Access 2000 file, I have put this down to possible database corruption in the past.

Regards!
0
 
Leigh PurvisDatabase DeveloperCommented:
Did/do you have a fully Service Pack'd Access 2000?

I wouldn't have been suprised by a big more growth in 2000, but certainly not to *that* extent.
0
 
JHVivianAuthor Commented:
fredthered - I also thought it was data corruption until I isolated the problem and am convinced it isn't in this case. Thank you for your conversion flow, I just tried it and the database grew to 447Mb, so a slight improvement but not the root cause.

LPurvis - I'm actually using Access 2002 SP-1 (which I doubt is the latest). I shall try to get the latest SP.

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jerryb30Commented:
Just a question to experts-Might this have something to do with differences between ADO and DAO?
0
 
JHVivianAuthor Commented:
jerryb30 - I am adding in the DAO 3.6 object library reference.  What has stumped me is that it behaves OK if there is no text field being changed in the process, which implies that there is nothing necessarily wrong with the methods as such.
0
 
Leigh PurvisDatabase DeveloperCommented:
Just a guess - but I'd have thought since this was a converted 97 application that DAO would have been used throughout anyway...

And I'd be pretty shocked if ADO had caused it.
(OK - shocked is a bit strong... somewhat suprised :-)
Ultimately Jet is reponsible for the appending and updating of records.

So then... what?  I suppose it's possibly a) a bug - service pack 3 may yield improvements  b) a Unicode consequence (again would be suprising to *that* extent  c) a Jet 4 difference (again perhaps service pack worthy - there are a *lot* of service packs for Jet - 8 last I knew... that's more than Access 2000 even! ;-)

Without seeing the full code it's difficult to speculate further. (Or less lol)
0
 
jerryb30Commented:
Wild guessing continues....I went through bloat issues several years ago, and my boss just chuckled when I mentioned 'bloat' and made some comments on monthly cycles, suggestion we only run the database programs at certain times of the month, etc.  So, I feel your pain.
I agree with leigh (always safe to do) that it is probably a bug ('undocumented feature').  I am guessing that at some point in the decision branch to write a text field that space is not being released from the original query, or rather that it is being somehow magically considered some part of the database at the end of the update.  If you wanted to verify this, you might place some break points in your original code, and see at what point the database grows.

0
 
JHVivianAuthor Commented:
Thanks for the interest and it gave me an idea to do some more analysis. I put a breakpoint in to stop every 1000 rows and interestingly it was increasing by exactly 4Mb every 1000 rows it was updating (4K per row if my maths is correct). This was consistent even if I increased my text field to 100 characters and filled every byte. Sounds like buffering gone mad.
0
 
Leigh PurvisDatabase DeveloperCommented:
4K per row would be the maximum size permitted.
What other data do you have in each record? (Besides the text field being updated or not).
Much?

Worth doing these tests - to look for differences with all updates subsequently applied.
0
 
GRayLCommented:
Are you sure all your code terminates properly.  ie.:
Set myqd = nothing
Set myrs = nothing
Set mydb = nothing

If you do not do this for all the objects you create in code while doing the manipulation, they can bloat you mdb.
0
 
Leigh PurvisDatabase DeveloperCommented:
Indeed - this crossed my mind too Ray.  (alluding to it in my earlier "Without seeing the full code").
But it would still be something of a failing for Access 2000 to bloat under such a scenario where 97 did not.

(But then 97 was/is a little beaut!)
0
 
GRayLCommented:
Maybe 97, being more primative, just tossed those things away when the mdb closed.  A2K, being much more sophisticated, just hangs on to everything?
0
 
JHVivianAuthor Commented:
When I put the breakpoint in, stopping it every 1000 rows, the recordsets are still open yet the growth was occurring, which discounts the code terminating theory I believe. Wrt the service packs, I ran it at a corporation under Access 2000 and they would have had the latest, yet it still happened.

I have made another discovery. As I said at the top, I  use sql query to build table2 from table1 with additional fields unpopulated, this is having emptied the table first. However, when I remove that build and start the function off with the table already in existence, the problem does not manifest itself.

Now I strongly suspect it is a genuine bug and do not intend trying to figure it out any more.

As I now have two ways round this I am going to thank you all for listening and hope that the thread may help somebody out there in the future.
0
 
GRayLCommented:
All that said, I do believe LPurvis answered your question as best he could when he said: "So then... what?  I suppose it's possibly a) a bug..."
0
 
JHVivianAuthor Commented:
Does declaring it a 'bug' qualify for points? This is my first post so not sure of the protocol! Happy to give them out......
0
 
GRayLCommented:
Any plausable answer that satisfies the asker is an answer.  If you are willing to accept that A2K could have that bug then sobeit.  Similarly, sometimes the asker asks how to do something, only to be told: "You can't do that."  That also is a valid answer.
0
 
JHVivianAuthor Commented:
Thanks for the advice, I shall award the points to LPurvis.

0
 
jerryb30Commented:
The only other option is to have the question deleted, and then nobody would benefit from the investigation and discussion.
0
 
Leigh PurvisDatabase DeveloperCommented:
If people would only stop giving me work - I'd have me a nice long play with Access 2000 to see.
(Well - that said... I never develop in 2000 anymore - so maybe not lol.  No - seriously it probably is worth a look.  We'll see.  Cheers all.)
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 6
  • 5
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now