Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Compiling and Bloating (Access 2003 SP2)

    I manage a database that bloats considerably. When I compact it I first compile the code, then run the compaction process. Each time I compact I have to compile the code again. Is there any relationship between Compliling and Bloating? I have noticed that a database that stays compiled bloats very little vs one that will not remain compiled bloats 20% or more over its compacted state. And another question would be, shouldn't compiled code remain compiled if it is not edited?
    Thanks
    chuck

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compiling and Bloating (Access 2003 SP2)

    A compiled database should remain compiled after compacting it. See if a decompile - compile - compact cycle helps.

    The thread starting at <post:=44,571>post 44,571</post:> explains about decompiling; there is a handy tip by MarkJ for adding a Decompile option to the right-click menu for .mdb files in Windows Explorer.

  3. #3
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Compiling and Bloating (Access 2003 SP2)

    I applied MarkJ's tip. Really made a big difference in size on several databases.
    Thanks
    Thanks
    chuck

  4. #4
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compiling and Bloating (Access 2003 SP2)

    This is an interesting article for some background on the decompile switch.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  5. #5
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compiling and Bloating (Access 2003 SP2)

    This has a lot of information and links regarding database bloat.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  6. #6
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Compiling and Bloating (Access 2003 SP2)

    Steve,

    As I was thinking of questions the links you gave me answered them all. I love this forum. Thanks so much.
    Thanks
    chuck

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Compiling and Bloating (Access 2003 SP2)

    This article about bloat includes this : [i]"Don
    Regards
    John



  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compiling and Bloating (Access 2003 SP2)

    Temporary tables do cause a lot of bloat, if you create a lot of them it may cause the database to grow to many times its original size in one day, but the extra space tends to be reclaimed completely by a compact and repair. It's up to you to decide what weighs heaviest.
    Compact on close usually works well, but occasionally, Access reports that it can't delete the original database, so you end up with the original database and a compacted one named db1.mdb or something like that. If the users are not Access-savvy this may cause problems.

  9. #9
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compiling and Bloating (Access 2003 SP2)

    <P ID="edit" class=small>(Edited by SteveH on 12-May-06 09:35. Remembered a link)</P>There was an article in SmartAccess back in about 2002 about using text files or somesuch instead of Access temporary tables. This avoided database bloat. I cannot find a copy of it anywhere on the interwebnetthingy unfortunately, so I cannot link to it. <img src=/S/frown.gif border=0 alt=frown width=15 height=15>

    <!post=Jan 2001,228,856>Jan 2001<!/post> apparently.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compiling and Bloating (Access 2003 SP2)

    I wrote a series of functions that I use in some of my databases for managing temp files. The concept is that each user has a second backend that is located in the same folder as the frontend, and is used only to store temporary tables. Moving them out of the frontend avoids FE bloat, and mounting the tempFrontEnd in the same folder as the FE ensures that each user has their own copy of the temp tables.

    The functions that I wrote are for creating the tables, managing the links, etc. One of the main features is that it can start by creating a new "tempBackEnd" upon demand, thus ensuring that the tempBackEnd doesn't bloat. Unfortunately (for you), there are no usage instructions, but you're welcome to use them in your own database.

    Very long code sample (over 11,000 characters!) moved to attachment by HansV to keep down load time for the thread
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  11. #11
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compiling and Bloating (Access 2003 SP2)

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    Looks like good code, but it seems to me that it would be a lot easier just to use remote queries to push the data into the remote local .mdb or retrieve from it. See topic:

    http://www.vb123.com/toolshed/02_acc...otequeries.htm

  12. #12
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compiling and Bloating (Access 2003 SP2)

    I simply fire a batch file in each client's startup folder that deletes client's frontend *.ldb file (if it exist) then refreshes clients frontend.mdb program with a copy from the server, which is the one I keep up to date.

    HTH, John

  13. #13
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Compiling and Bloating (Access 2003 SP2)

    Most of my clients do not have servers. Some just have a single laptop, and are located a long way away, so updating the front end is not a simple process.

    I find my sparing use of temp tables for importing (and even more sparing for reports) in conjunction with compact on close works OK and does not seem to result in bloat, so I will probably just keep on doing what I am doing.
    Regards
    John



  14. #14
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compiling and Bloating (Access 2003 SP2)

    However when you do update the FE mdb on client laptop you could store a master copy of FE.mdb in a C:Mast_Mdb directory on laptop and refresh FE.mdb from C:Mast_Mdb.

    The advantage to this would be if the client's FE.mdb ever gets corrupted (and is a long way away), a simple reboot would refresh the front end and solve the problem.

  15. #15
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Compiling and Bloating (Access 2003 SP2)

    A lot of useful advise <img src=/S/smile.gif border=0 alt=smile width=15 height=15>.

Page 1 of 2 12 LastLast

Posting Permissions

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