Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    What causes .mdb file to grow? (Access 97)

    Hi Lounge Lizards ... I have been tracking the size of an .mdb file for about 6 months and there are huge spikes which are not related to increases in traffic or data. For instance, right now is our slowest period and the database size hit a new all-time high. I'm attaching a chart - you can see that we compact it once a week.

    Our boss is asking, "Why?" when he looks at this chart ... and, "I don't have a clue." is not the answer he's looking for. Can anyone help????
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: What causes .mdb file to grow? (Access 97)

    Any time you edit a record, or add new records, the database grows. You said you compacted it every week, but it grew from week before last week to last week to this week - or was the measurement from last week taken before the compact was done? In any event, one thing you might try is importing everything into a new empty database. Also is your database split? If not you should probably do that - see our tutorial on why it's a good idea. If indeed you've done all those things, then you might start looking at putting individual tables in different database - or - you may need to look at SQL Server. Hope this makes sense - if not post back.
    Wendell

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What causes .mdb file to grow? (Access 97)

    Hi Wendell,
    Yep - its split. I'm really interested in what you said about it growing every time you edit a record. You see, we're not worried that its getting too big - over the last 6 mths it's lowest point was 160MB and highest was 290 just yesterday and the trend line shows overall growth of only about 10MB. I just need a way to explain to the owner *why* the size goes up when overall activity on the database is assumed to be going down. I'd like to be able to say, "Don't worry - this is just the way Access is" - I wish I'd never started charting the daily changes in size because its causing the client [probably needless] worry. On the other hand, this is the only way I know to figure out how fast it is actually growing.

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

    Re: What causes .mdb file to grow? (Access 97)

    It probably *is* just the way Access is.

    If you have indexes on your tables (and if you don't, Access will probably have created hidden ones), a seemingly simple edit of a record could necessitate a major overhaul of indexes. In such a case', Access probably (I'm speculating now) expands the database to make room for a new version of the index, but it doesn't release the space taken up by the old version. Similarly, if you have relationships with cascading updates, a simple change in one table can trigger a series of updates in other tables. ther is probably more in this vein.

    Combined, it means that the impact of a small change can be negligible or substantial - it depends on the context of the change.

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: What causes .mdb file to grow? (Access 97)

    Just a couple of other thoughts to add to the suggestions from Wendell and Hans.

    Do you have any objects in your database; that is, pictures, etc.? Do you have any temp tables, in which the data is frequently being deleted/added?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: What causes .mdb file to grow? (Access 97)

    Another factor than can cause disproportionate increase in size of an .MDB file is refreshing linked tables programmatically. I have one application where certain linked tables (5 in all) are relinked to a different back-end .MDB using the DAO TableDef RefreshLink method. After running sub to relink tables, the database increases in size by almost 1.2 MB. When relinking same 5 tables "manually" using the Linked Table Manager, the db increases in size by only about 140 KB. In either case after compacting database it returns to original size. This behavior occurs in both A2K and AXP (A2K file format); I no longer use A97 so am not sure if this occurs in A97 as well. I have not seen any explanation for the disproportionate increase in size when using RefreshLink method.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington (Greater Manchester area)/Cheshire, UK
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What causes .mdb file to grow? (Access 97)

    Something that I have discovered lately while running QA tests on our Access application under Windows 2000 and Windows XP is that you do not need to actually change any data yourself for the database to grow. All you have to do is browse through the forms, click a few buttons, view some reports, etc - doing nothing that will actually change data and you will notice that the mdb will grow.

    What happens, is that each time you access an object, eg. open or close a form, click a button, run a query, open or close a form, etc; the time and date of that action is recorded in the database. The size of the time and date stamp may also vary depending on the Windows Regional Settings.

    We have the test results to prove it and it has caused pure and unadultarated torture for us in setting up a standard baseline system with which we can compare further tests and it took about 3 months to find this stupid information out, so I know exactly how frustrated you feel.

  8. #8
    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: What causes .mdb file to grow? (Access 97)

    I saw an article somewhere (I have it at home) which showed by how much a db bloats when you do things such as change recordsources programatically, run queries etc.. Running queries will cause some bloat when the Rushmore optimisation kicks in. It has to be stored somewhere. I'll try to find the article and post it or post a link to it. It was written by someone who developed a method of avoiding db bloat from temporary tables by using csv files instead of Access tables. HTH
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: What causes .mdb file to grow? (Access 97)

    Are you thinking of the article "Temporary Tables with No Bloat", by Doug Den Hoed (Smart Access Jan 2001)?
    Charlotte

  10. #10
    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: What causes .mdb file to grow? (Access 97)

    Yep! Thats the one.
    Good call Charlotte! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: What causes .mdb file to grow? (Access 97)

    I built a demo based on that article, so I remember it well. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> Unfortunately, the demo is too large to post here.
    Charlotte

  12. #12
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What causes .mdb file to grow? (Access 97)

    Thank you all so much. Yes, it does use lots of indexes and cascading updates. Its No, Mark, its doesn't have picture objects yet, but probably will later, and yes, quite a few temp tables that are created and destroyed using program code. And, yes, there is code to re-link linked tables programmatically. When I first started charting its size, I concluded that we'd have to move to SQL server within 2 years - now after charting for 6 mths, its clear we're growing slowly and can stay in Access for years to come.

    The .mdb has 95 tables, 534 queries, 87 forms, 59 reports, 6 modules and approx 20,000 lines of code. I'm going to summarize everyone's comments above and conclude that the inexplicable increases in size are due to the large number of objects that gets affected even if users are only viewing data.

  13. #13
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What causes .mdb file to grow? (Access 97)

    Steve - I found the article - http://www.larkfarm.com/sax/sax2-3.txt. Thanks.

  14. #14
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: What causes .mdb file to grow? (Access 97)

    Additional note: As experiment, tried replacing old-fashioned DAO RefreshLink method with new-fangled ADOX code, using ADOX Table object's "Jet OLEDB:Link Datasource" property to reset path for linked tables. Besides running noticeably slower, after same 5 tables were relinked using ADOX code, the .MDB had bloated in size fm appx 4036 KB to appx 6648 KB, an increase of appx 2.6 MB in repeated tests. That's an awful lot of overhead to store 5 crummy little connection strings.... So if anyone is interested in knowing how to refresh linked tables in a slow, inefficient manner, see MSKB article ACC2000: How to Use ADOX to Create and Refresh Linked Jet Tables for more details.

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: What causes .mdb file to grow? (Access 97)

    Ah, but with ADO you can dispense with linked tables altogether ... unless you want to use bound forms of course. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

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
  •