Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access File Growth - Causes? (Access2k, Win2kPro)

    I know this subject has been covered in the past, but lack of search engine makes it difficult to find previous discussions.

    Question: when I compact and repair a db, the file size is 816K. I've noticed that adding new records tends to make the file grow rather quickly (2K+ with four records). I'm not sure if this is going to be a big issue or not and am curious as to what things make Access bloat.

    This particular db has a lot of Lookups or two main tables and, just to be consistent, I allowed Access to create Key fields for every table. Could this be causing the bloat? I normally don't declare Key fields unless I feel it is necessary so I am sort of experimenting in this project. Other than that possible problem, the db is relatively simple: one Client Information table/form with subforms for Intake and Discharge. I haven't had a db bloat as quickly as this one so i tend to think all those primary keys are causing a lot of 'back-end' tracking that gets reset with a Comnpact and Repair.

    I haven't loaded any reports as I need to hand it off to the client for preliminary testing of the UI.

    At this time, it seems to work satisfactorily but it does seem to like to put on a lot of weight.

    Any general advice on Access file size issues and underlying causes?

    TIA!

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

    Re: Access File Growth - Causes? (Access2k, Win2kPro)

    You have several things that are, or could be, an issue. All the temporary queries Access creates for reports are certainly one cause of bloat, but so are action queries, since they do not release the space they use after execution. The most common cause for bloat is Name Autocorrect, which can be turned off from the Tools-->Options menu and should be. I don't know what you mean by "a lot of lookups on two main tables", but is you are using subdatasheets, that can do you in as well.
    Charlotte

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

    Re: Access File Growth - Causes? (Access2k, Win2kPro)

    Your post was a tad confusing. Do you have a split frontend/backend situation? And if so, in which database are you experiencing bloat?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    New Lounger
    Join Date
    Feb 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access File Growth - Causes? (Access2k, Win2kPro)

    What I mean by "a lot of lookups on main tables" is, you create a table, and define a field using Lookup Wizarfd to bind that field's values to another table. Not an uncommon scenario; what i have done in this db is allow access to define a primary key for every table, even if a given table is only used as a row source for another table's field values. I was wondering if maintaining primary keys for every table might cause file bloat. That's just a guess, however.

    I am not doing any reports yet, so that shouldn't be an issue. I don't know what an action query is, but I am only using one query in the db so maybe that's not an issue. I'm also not using subdatasheets but am using subforms. Not sure if that would be an issue. I checked on Name Autocorrect and it is on. Since that seems to be a default, and I haven't noticed much file bloat before, what, precisely, is it for? Never heard of it.

  5. #5
    New Lounger
    Join Date
    Feb 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access File Growth - Causes? (Access2k, Win2kPro)

    Sorry to be confusing. I wasn't really trying to get a specific problem solved but to have a general question addressed. Mind you, the issue might be of real consequence specifically: in the course of developing this db, at one point the db file was 28,000K from an original (or compact and compare) 800K. And that was before loading much in the way of dummy values. BUT I was interested in feedback in general re. how and why Access files get unreasonably large.

    Having said that, to answer your question, this is one .mdb file. The stucture is simple: a Client Information table, a Intake table, a Discharge table, and supporting lookup value source tables for drop-downs. I have the Client Info form as a container for Intake and Discharge subforms. the subforms are complex and draw a lot of info from the supporting lookup table values. I've done similar projects in the past, but for this one I went ahead and let access assign primary keys to *every* table. My question is, does this cause bloat? And if it doesn't, what else might?

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

    Re: Access File Growth - Causes? (Access2k, Win2kPro)

    <big>Turn it off! </big> It was supposed to be a handy feature to keep track of the names you give things and to automatically correct those object names in other places. For instance, if you renamed a table, any query that used that table is supposed to pick up the new name. Unfortunately, it has to track all those changes and the log it keeps contributes to a huge amount of bloat. Furthermore, it has been implicated in nearly every truly strange behavior I've ever seen in Access 2000. It was intended for end users, not developers, and it was a BAD idea from the start. Just because it is on by default doesn't mean it's a good idea. Subdatasheets are also on by default and they can have a huge impact on performance, especially when a network back end is involved.

    All those lookups generate temporary queries, and that can certainly contribute to bloat. Lookups in tables can be useful in the development stage, but they are overkill once the app is built. Since your users should never see tables at all and you should not rely on lookups in queries, they don't serve any real purpose down the road.
    Charlotte

  7. #7
    New Lounger
    Join Date
    Feb 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access File Growth - Causes? (Access2k, Win2kPro)

    alright, I've turned off Autoname. It sounds a little lame anyway -- if you rename something it's up to you to track the consequences and depending on MS to be thorough is VERY naive.

    I understand what you mean about using Lookups in development, but my client likes knowing he can go to a table and add/delete items as needed so I need to keep that option open.

    Well, since it's early in the dev cycle I am not terribly worried about file bloat -- things will likely change before the project 'goes gold'. but, knowing Autoname to be a bad (and default) setting is worth a lot as is. Thanks!

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

    Re: Access File Growth - Causes? (Access2k, Win2kPro)

    If you allow your client to go into tables and delete things himself directly, you had better be prepared for a LOT of support calls when he screws up the data.
    Charlotte

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

    Re: Access File Growth - Causes? (Access2k, Win2kPro)

    The reason I ask the question was because there are different causes for FE bloat vs. BE bloat. In FE, opening object in design mode, and running queries will cause bloat.

    In the backend, frequent updates of data, including changing indexed field values, may cause some bloat. The 2K "bloat" you saw by just adding 4 records was probably just the minimum disk segment Windows will allocate when a file needs to grow. Having Windows define PrimaryKeys is NOT a cause of your bloat. This generally will help your application's performance, as most database engines like to be able to uniquely identify records. And while Access doesn't require unique reocord identifiers, other database engines do.

    And you should really split your database.

    This is all in addition to the points Charlotte mentioned.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access File Growth - Causes? (Access2k, Win2kPro)

    not too worried about that. my client is the one who originally designed the forms i am automating and he's aware of what tables would be good to add/delete information in and which ones aren't. i mean, this is the drop-down info anyway.

  11. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access File Growth - Causes? (Access2k, Win2kPro)

    what is this database splitting you speak of?

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

    Re: Access File Growth - Causes? (Access2k, Win2kPro)

    "Splitting" a database is the process of separating the data from the programming. The database is split into a backend database and a frontend database; we typically refer to these as BE and FE. The BE contains the tables and relationships. The FE contains the forms, queries, reports, code, etc. There is even a Database Splitter Wizard to guide you thru the process.

    Splitting a database has many advantages. In a networked situation, you put the BE on the file server, and put a copy of the FE on each user's local drive. This eliminates contention among users in the FE, and helps performance as everything Acces needs (except the data) is retrieved from the local drive (which is quick), instead of from the network (which can be slow). You will have less database corruption. FE bloat will not affect the backend, and even that is easy to cure; I typically have my users download a fresh copy of the FE database each time they boot-up (it is done automatically).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Access File Growth - Causes? (Access2k, Win2kPro)

    In addition to Mark's comments, you might find our tutorial on database splitting useful.
    Wendell

  14. #14
    New Lounger
    Join Date
    Feb 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access File Growth - Causes? (Access2k, Win2kPro)

    thanks for the clarification. I don't think it's necessary for this project as it won't be networked and is very simple (3 forms) and, at some point, a small library of reports that don't have to be run very often.

    I'll keep it in mind, tho. at some point I may have to build a client/server db and that sounds a little more trouble-free than getting into workstation install solutions. as far as the bloat that started this thread, i'll just wait and see how the db behaves. one thing i have picked up is that, somehow, Lookup Tables are bad. that is troublesome because they are very handy: what better way to populate a drop-down and change the drop-down values? I *don't* want to go the route of hard-coding drop-down values so am resistant to the statement that look-ups from another table are ipso facto verbotten. perhaps, if this is an issue, it is the way Access wizards map tables to drop-downs; if there's a better way willing to learn.

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

    Re: Access File Growth - Causes? (Access2k, Win2kPro)

    Size isn't the issue in splitting a database. I split all my databases except demos that are intentionally kept in a single file. The main reason for splitting, aside from performance on a network, is because interface objects are much more likely to corrupt than data. If there is any critical data, modifying the user interface could lead to a situation where the database itself is corrupted and you can't do much except regret that you didn't split it before it became an issue. With a split database, if the front end (the forms, reports, queries, etc.) get corrupted, you can restore a backup without losing data as well. With a single file, restoring a backup means that you lose any data entered and any data changes made since that backup. That does NOT usually go over very well with clients.
    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
  •