Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2008
    Posts
    5

    Angry Unanswered: Linked table database grows exponentially!

    We recently upgraded a frequently used and accessed database of ours from MS Access to MS SQL 2005. To accommodate our current applications that was written to work from an Access back end, we linked all the SQL tables through to Access and pointed all apps to the Access database. Everything works perfectly fine, but for some reason the database file (.mdb) grows from about 2.8MB to 1.1GB in about a week!

    I just dont understand why this file is growing as there is absolutely no data being stored in it and it simply contains links to the SQL database. Very frustrating to have to do a compact and repair so often.
    Any bright ideas?http://www.dbforums.com/db_images_v3...s/confused.gif

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Something is being written or updated to the MDB file often enough for file-bloating to occur. Perhaps some operations are being done by creating tables, doing something and then deleting them. Even modifying a form and saving it -- even in code -- will result in file bloating.

    What version of Access are you using? Access 2007 is shocking for file size bloating and one of the best ways around it is to use PKStormy's cloning file launcher. That effectively re-copies the original unbloated application to anyone who starts it. The approach effectively destroys all front-end file size bloating / corruption issues in one fell swoop.

    Check out the code bank and see if you can find it
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Dec 2008
    Posts
    5
    You're right, there are some temporary tables being created and deleted and also data being written VERY frequently from a number of applications. What we cannot afford to happen though is that there is any break in connection to the database (like the database being compacted and repaired) as this will result in applications bombing out and operations being interrupted.

    We're using Access 2003. Is there no setting in Access itself that one can change to avoid this?
    Sorry but i dont really know what the code bank is . Tried to Google it but no results...

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so its sounds to me as if you haven't really swicthed to a server back end thoroughly.

    apart for a redesign (and using pass through queries for everythign), the only fix I can think of is to do an reqular timed compact and repair (say overnight after the backup has run

    and reconsider how these temporary tables are created. If you must create them in JET then consider deploying a MDE to each users desktop / workstation. then let them do what ever the heck they want

    the code bank StarTrekker is referring to is the code bank in this Access Forum.. its the second or third thread on the first page of Access
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Dec 2008
    Posts
    5
    Thanks healdem, that sounds like a good place to start... I'll go through the code again and make sure that everything is pass through queries. I'll also have a look at the code bank StarTrekker suggested.

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Just for clarification here,... the MDB file that is bloating out, is it a front end or a back end?

    I thought you said the backend has been upsized to SQL Server? If it has, you only have to deal with the front end by what I have suggested and that's it.

    But if it is a back end MDB, then you have a problem as you will definitely have to be doing an automated compact and repair, daily, at a time when the fewest people are on. OR complete the upsize and have all your back end on SQL Server. TBH, in my book you wouldn't have a choice as daily compact and repairs, even automated almost always fail because some user has failed to comply to the "Make sure you close the xyz application before you leave" directive, leaving the back end locked. You also should not be doing any temporary read/writes to the back end at all. All that stuff should be done in the front end.

    BTW, this is what I was referring to
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I've seen this kind of bloating on SQL Server if you don't have your backups and tranlog backups configured. Make sure to also configure tranlog backups. Once you've created a maintenance plan, SQL Server will handle shrinking down the size again (it's an option when going through the wizard.)

    Ooops - sorry - I thought you meant your SQL Server database grew in that size (not the mdb). But if all else fails, check out the size of SQL Server files (*.mdf and *.ldf I believe are the SQL Server extension names of where you saved them.) This can grow as well and have an impact on your mdb.

    If you're doing a lot of writes/deletions to SQL Server tables and don't have a Maintenance plan (where you configure backups/db checks/indexing plan/logs/etc..), you can expect the SQL Server db's to grow. That's what the Maintenance plan takes care of.

    Also, put your larger temp tables on SQL Server and keep your smaller temp tables in MSAccess.
    Last edited by pkstormy; 12-08-08 at 23:10.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Dec 2008
    Posts
    5
    I have SQL server as my back end but have an Access database as a 'middle man' with just a bunch of linked tables to my SQL database so that my applications think they're still working through Access.

    Thanks for all your help and suggestions. I'll have to take a collective look at how i can change and tweak certain parts of the whole setup now that i have some light shed on this whole thing. I'll also pay more attention to what is happening in SQL server as pkstormy has suggested as i thought this has absolutely nothing to do with it.

    Thanks again, much appreciated!!

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Get rid of the middle man. Just link your SQL tables directly in your FE.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Dec 2008
    Posts
    5
    Thats the idea... but there is roughly about 7 biggish apps that need to be changed to work off a SQL backend and that will take a little time as our resources are very limited right now... but yes... thats ultimately what we're aiming for.

Posting Permissions

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