Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Another anomaly? (Access2003)

    Ok, here is another one. Although I'm not really sure it is cause by Access itself.

    One of my clients is a software house, and they just released a new version of their software, installing it at several sites for final testing before the general release. At one site, they ran into 2 bad problems:
    - The backend MDB file experienced regular and severe bloating, going from 55MB to 2GB in a matter of hours!
    - One particular form was taking a loooong(!) time loading; although sometimes fairly quickly, other times it took seemingly forever.

    The backend MDB is sitting on a Windows 2003 server. All users were using Access2003; but some were local to the server, while others came into the server via Remote Desktop. Everyone had own copy of FE. As best as we could determine, everything had latest versions and patches.

    In the database, there was already a Persons table and an Activities table, and the new version added an Experience table. For a small subset of all Persons (some 40 out of 7000), there was 1 record in the Experience table for each Activity (a classic "resolver" table to handle a many-to-many situation).

    The form in question was doing a "just-in-case" adding of activity records to the resolver table (since there were several ways new Activities could be added), relying on the Unique Key of PersonID and ActivityID to keep duplicates out of the table. Apparently it was this query that was causing all the problems! For some reason it was causing Access to try to get more space, and the process of getting this space delayed the form from opening.

    Here is what I have surmised. The query basically determined there were some 12,000+ records that would be added to the Experience table (each of 40 persons and some 300+ activities). Access basically then went out to get enough room to add all these records. Only after getting the space and actually trying to add the records did Access realize that not all the records would be added. But the space had already been asked for and allocated; and this happened each time the someone opened this form and the query ran.

    I have a vague recollection of how Access allocates space. I believe starting in Access97, Access no longer just added all new records to the last sector in the file (this often caused a contention problem). When new space is allocated, it essentially includes a buffer area to allow for future records being added to these areas rather than always at the end.

    So it appears that new space was constantly being asked for, but then never used! And apparently Access didn't know it, which is why it kept asking for more. Now, I don't know if it merely Access2003, or the combination of Access2003 with Windows Server and/or with the use of Remote Desktop.

    Anyway, I rewrote the query to only add records not already in the Experience table, and we haven't had any more problems with bloating or performance.

    So, this is just something else you can keep in the back of your mind!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Another anomaly? (Access2003)

    Access 2000 and above can bloat very quickly, and 2002 and above format files grow even quicker since they contain a system table that does NOT compact and that does not exist in the 2000 file format. Under the hood, Access creates a lot of temporary queries and that results in all that space being allocated. No version of Access has ever released allocated space without a compact being run.
    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: Another anomaly? (Access2003)

    This was the backend bloating, not the frontend, and aren't the temp queries in the frontend? And the BE is in Access2000 format. And it isn't a question of releasing the space, it almost seems as though Access doesn't even seem to realize it has it and can use it. (More please!)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Another anomaly? (Access2003)

    Yes, the temp queries should be in the front end and that sounds extremely weird. I don't have 2003 so I can't guess what might be going on there unless their code is actually performing queried in the back end for some obscure reason.
    Charlotte

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

    Re: Another anomaly? (Access2003)

    I think your description of how space is allocated in Access/Jet is essentially correct, especially if multiple users are doing it concurrently. You might also check to see if the OnCurrent event is contributing to the grief. I've seen sets of code get run many times in short order - and very often that event will trigger at least twice. Just an outside possibility.
    Wendell

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

    Re: Another anomaly? (Access2003)

    Hi Mark:

    Since you seem to understand this Access bloating phenomenon, perhaps you can advise me on how to roll it back!

    I have a split front-end back-end application in production. In January, we "upgraded" from Access 2000 to Access 2003. Since then, the backend has bloated from 34meg to 700 meg, compacted, although it is still in Access 2000 format. We are running on XP and Windows 2003 X64 server.

    The front end presents some user forms allowing users to run customized reports on the backend tables. The backend imports tables from a commercial SQL server database via an .adp file plus a second commercial software that uses an .mdb backend, using the transferdatabase method. So every day, my backend code deletes its 36 imported tables, reimports them from the source databases, sets indices and relationships on the imported tables to the permanent tables, and performs calculations via DAO and VBA code and update queries. There are about 80 permanent tables, of which about half are regularly emptied and filled bythe update procedures, and 15 modules, none of which is over 64 k.

    Everything works fine, but I am getting increasingly perturbed by the bloat, which increases steadily and does not go away with compiling. I have added code to the imported tables to set the text fields' unicode compression to true and the tables' subdatasheet name to [None]. I have added code to index fields that are used in queries (both back-end and front-end) to speed things up. I went through all my dao code and explicitly set all my recordset and database objects to close and set their variables to nothing at the end of each procedure. I have set the backend to compact on close and run a decompile, compact , compile and re-compact weekly. I've also used the compact and repair utility, but it doesn't seem to have any effect.

    Can you suggest how else this bloating can be brought under control? Also, do you know if the 2 gig limit on .mdb files has changed? I feel like a lemming heading for a cliff!

    Thank you, in advance, for your help.

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

    Re: Another anomaly? (Access2003)

    I don't know a real solution for your problem, but you can often reduce the size of a database by importing all database objects into a blank new database (make sure to import the relationships too), then compacting the new database. If it helps, replace the old database with the new one.

    BTW I assume that your backend database contains tables only. If so, decompiling and recompiling will have no effect - it applies to VBA code only.

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

    Re: Another anomaly? (Access2003)

    Thanks, Hans, but I've already tried the new database idea, using the compact and repair utility and also creating a blank db and importing everything via the wizard. And yes, my back-end db has LOTS of vba code, in 15 modules.

    Any other ideas?

    Thanks for your help!

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

    Re: Another anomaly? (Access2003)

    If importing into a blank new database, then compacting doesn't help, your backend really IS that big. I don't think there is anything else you can do (except perhaps going back to Access 97, which is not very attractive).

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

    Re: Another anomaly? (Access2003)

    Access 97 always worked for me, but it's a bit late now . . .
    How could an mdb grow from 34 meg to 700 meg in 4 months using basically the same code?

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

    Re: Another anomaly? (Access2003)

    I must mean that there are more data, e.g. in the imported tables.

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

    Re: Another anomaly? (Access2003)

    Okay, I answered my own question. One of the sql server tables that gets imported has been expanding rapidly. I tried deleting it from a copy of my back-end file, and it shrank from 700 meg to 74!

    You were right, as usual, Hans!

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

    Re: Another anomaly? (Access2003)

    I would not import these into the backend. I think Hans suggested importing them into a blank database. I'd have a separate database just for these temp tables.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Another anomaly? (Access2003)

    Is there a reason why you can't simply link to the SQL Server tables using ODBC instead of importing them? That gives you current data all the time, and your database won't bloat like it currently does. Also, 2GB is still the MDB limit and isn't expected to change.
    Wendell

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

    Re: Another anomaly? (Access2003)

    Well, that's a thought. I didn't try it initially because of three factors; first, my application has a number of permanent tables that would have to be linked to the SQL Server tables; second, I'm also using tables from a second commercial software with an .mdb back-end (which would also have to be linked) and third, the powers that be around here are very nervous about my doing anything that could affect the commercial software or its data. I thought that having a front-end reporting database that links to a backend which links to two other back-ends, one on the SQL server, would be unwieldy. And finally, I would rather run update code at night on the back-end than have users creating record locks on the commercial software's tables during business hours, so that would mean using disconnected recordsets, which is a lot more development work in an area I haven't tried before. And one always has to think about performance if users are pulling records via local queries on a networked back-end that pulls its records from two other sources.

    How would you suggest going about it, and what do you think the performance results would be?

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
  •