Results 1 to 11 of 11

Thread: 900mb MDB

  1. #1
    Join Date
    Feb 2010
    Location
    Yorkshire UK
    Posts
    76

    Unanswered: 900mb MDB

    Hi All,

    A friend has asked me for help and thought I would ask you guys and then pretend I am cleverer than I am!

    His database size is 900mb. When it is compacted and repaired, it shrinks to 147mb. Within 48 hours it creeps back up to 900mb. This is slowing it down to a crawl.

    From what I can tell, they have a huge table of customer orders. Stock is calculated from this table (StartingBalance - TotalSold = CurrentStock)

    I have advised to split the table into old and new and to add a brought forward balance into the new table.

    This database has both the FE and BE in one application and 8 users are accessing it over the network. I have also advised against this.

    What are your thoughts?


    Thanks
    Simon

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Having the app and data in one file will cause bloating especially if there are recordsets etc.. I would not advise refactoring the database yet - separate into FE & BE.

  3. #3
    Join Date
    Oct 2009
    Posts
    340
    a multi user db must be split into FE/BE - - on this point there is no choice. It is a fundamental design requirement by Access. This application is implemented wrong and is the cause of their problem without a doubt.

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    What they said! Besides bloating, multiple-users on a single, non-split database exponentially increases the change of corruption! Splitting simple has to be done in this scenario, with a copy of the front end on each user's machine and a single, shared back end on the sever.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    May 2010
    Posts
    601
    Adding to the already excellent advice, I would also compile the front end in to a MDE/ACCDE.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  6. #6
    Join Date
    Jul 2006
    Posts
    30

    Cool Bloat

    We have the same problem on a Citrix server. Best to auto compact on shut down each night. You can also have all users log off at lunch and let the db auto compact. We have the problem with the FE only. The BE does not have excessive bloating. I agree, you should have a BE and FE. You should also review your db indexing and field size.

  7. #7
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    What method are you using to calculate the stock e.g query etc. Is this your method ? A customer orders a stock item then the Current Stock balance is calculated, now another customer orders the same item then the stock is recalculated. This method also causes bloating.

    A suggestion would creating a temporary table in the back end with the current balance of all stock items at the beginning or end of the day overcome your problem? This table would also record any new stock or ordered stock transacted during the day and would make it quicker for your orders. Note the transactions for the current day would also be entered into your main stock table.

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by jimgriggs View Post
    ...Best to auto compact on shut down each night. You can also have all users log off at lunch and let the db auto compact.
    If by 'auto compact' you mean use the Compact on Close option this is a very dangerous idea. While many people experience no problem, empiric knowledge for years has suggested that Compacting is, in itself, a cause of corruption, and hence should only be done after a back up file has been generated.

    As has been suggested, constant bloat of the magnitude reported here may well be the result of flawed database design, and this is what the OP needs to address first, including the splitting of the app.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  9. #9
    Join Date
    Oct 2009
    Posts
    340
    when in a correct FE/BE split environment; if the FE is growing alot due to normal use - - then there is a design issue that needs to be resolved. Emphasis on "alot" and continuous - - thus after some days of normal use it will be so big as to max out and freeze up.

    if it expands a little at first and then pretty much stays the same - there is no cause for worry.

    for the ever expanding FE problem; One place to look is whether or not temp tables are being used - - - if so you want to be sure to explicitly delete them or clear their records with deletequery.

    a corrupt form can contribute also but finding this is more hit & miss in terms of troubleshooting....

    the 'compact on close' feature is better than having it bloat so big that it freezes - - but as posted it has issues too.

  10. #10
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I would suggest splitting the database into FE and BE as well. But I would also suggest separating the large table into its own MDB. You can have as many BE databases as you want. We had an application that had one very large table and 40-50 other tables. We put one table in one MDB and the other tables in a second MDB and then linked them to the FE MDB.

    The other thing to consider is why the database is bloating so quickly. Are they updating most/all the records in the large table on a daily basis? My understanding is that Access will make 'copies' of the current data before updating it so that it can UNDO the change. I would check to see if you could somehow limit any unnecessary updates to that large table.

  11. #11
    Join Date
    May 2010
    Posts
    601
    Something else that causes bloat: entering design mode for a form or report.

    Since the database is not split, if a you open an object in design mode, you will automatically cause the database to bloat.

    As everyone has already pointed out, you need to split the database.Also compile the front end into a MDE/ACCDE as previously recommended. If you will deploy the MDE/ACCDE to the users, then they can not enter into design mode which will eliminate onr of the causing for a database to bloat.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Posting Permissions

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