Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2004
    Location
    Edmonton, Canada
    Posts
    72

    Unanswered: DAO Loop rs.Edit Bloats Database

    Hi crew!
    We've got a 40MB database, one of the tables is 60k rows, and 75 fields wide. We have a DAO loop that simply does an:
    rs.edit
    rs!field = rs!field1 * rs!field2
    (does this for about 20 of the fields)
    rs.update

    Once it's done looping thru the 60k rows, the dB is 350MB!

    What's with that?

  2. #2
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Are you closing the recordsets and database?
    Code:
    Dim myDB as DAO.Database
    Dim myRST as DAO.Recordset
     
    Set myDB = CurrentDb()
    Set myRST = myDB.OpenRecordset("tblData")
     
    myRST.Close
    myDB.Close
    See this knowledge base article for more info;
    ACC2000: To Help Prevent Database Bloat, Explicitly Close Recordsets

    I even set them to nothing after the .Close statements (i.e. clear the defined objects).
    Code:
    Set myRST = Nothing
    Set myDB = Nothing
    Last edited by Cosmos75; 07-16-04 at 17:01.
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  3. #3
    Join Date
    Jul 2004
    Location
    Edmonton, Canada
    Posts
    72
    Yup, did the rs.close and db.close

    compact and repair brings it back down to the starting size. ???
    Thanks,
    Carl

  4. #4
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Compacting the database should bring down the file size, if you've never compacted before and/or have added and deleted a lot of records/object as Access gets rid of all the memory it holds on to for those deleted records/objects by creating a new copy and importing the tables/objects into a new file and renaming it the same name.

    So, you already had the .Close statements and your database still bloats?
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  5. #5
    Join Date
    Jul 2004
    Location
    Edmonton, Canada
    Posts
    72
    yes, I try never to forget the .close

    Thing that really bugs me is the loop is just editing records in the recordset, not doing any .addnew or .delete

    I've been using Access since the initial release and haven't seen anything this bad before.

    Thanks again for all your support,
    Carl

  6. #6
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Question

    could you test using ADO, just to check? Not logical solution perhaps, but if you have some sort of starting corruption etc., some functions can trigger this, and another might not?

    D.
    Win-XP pro, Access 2002, ADO 2.7, DAO 3.6. English versions of apps/OS.

  7. #7
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Arrow

    And, I would try importing just definitions/modules/etc., no data - to a new db, then populating the new tables with append queries, to see if a fresh one behaves in the same way.

    Also, you should test on two different computers, just in case...

    D.
    Win-XP pro, Access 2002, ADO 2.7, DAO 3.6. English versions of apps/OS.

  8. #8
    Join Date
    Jul 2004
    Location
    Edmonton, Canada
    Posts
    72
    kedaniel, aren't you just full of bright ideas! I owe you a big wet lick for that one.

    Here's what might've happened: Boss guy created the .mdb with Office XP Pro. When he gave it to me it behaved the same way on my Access 2000 as it did with his Access XP.

    I followed your brilliant suggestion of creating a new .mdb and appending the data into blank def's, and the bloating problem automagically disappeared (I should mention, boss guy tried importing all the objects into a new .mdb without success, the tip about leaving the data behind was the clincher). I should also mention, what used to take 5 minutes to compact now takes about 1/2 minute. The procedures that took 10 minutes to run, now take aobut 1/2 minute as well. Lovely.

    Thanks heaps,
    Carl

  9. #9
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Smile

    glad to help, that's what this place is for :-)

    D.
    Win-XP pro, Access 2002, ADO 2.7, DAO 3.6. English versions of apps/OS.

  10. #10
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Quote Originally Posted by carlmal
    kedaniel, aren't you just full of bright ideas! I owe you a big wet lick for that one.

    Quote Originally Posted by carlmal
    I followed your brilliant suggestion of creating a new .mdb and appending the data into blank def's, and the bloating problem automagically disappeared (I should mention, boss guy tried importing all the objects into a new .mdb without success, the tip about leaving the data behind was the clincher). I should also mention, what used to take 5 minutes to compact now takes about 1/2 minute. The procedures that took 10 minutes to run, now take aobut 1/2 minute as well. Lovely.
    Nice one, kedaniel!

    I wonder what was causing the bloat? Gonna try to look around in the KB articles or on other forums for a similiar problem.
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •