locked
Compacting the Front End of a Split Database Without Compacting the Backend RRS feed

  • Question

  • I think the title says it all, I've searched the net on and off for the past few weeks to get an answer to this question but although I've found many different methods I haven't found anywhere that confirms a method does exactly what I want. To compact the front end of the database but leave the backend file untouched.

    My reason for wanting this is because the frontend contains a large number of local tables in which the data is frequently being appended and deleted, and I'm expecting the file size of the frontend to increase quite quickly as a result of this.

    I'd like the database to compact the front end file whenever it is closed, so that users don't need to worry about it, but leave the backend alone. Then I can impliment function that will compact the backend file on request that we use after we've done our backup.

    At the moment I've asked for a procedure to be put in place in our office whereby at least one user is logged in throughout the day and then during the 'closing down for the day' procedures everyone logs out bar one user who does the backup before closing the database, when the 'compact on close' compacts their front end file as well as the backend file.

    Can anyone help me with this?

    Friday, June 8, 2012 2:58 PM

Answers

  • First,

    You should only have to compact and repair just before distribution in almost all cases. yours is an exception due to the build it sounds like.

    Second,

    You will have much anguish with your db if you compact and repair on close in almost every case. Yours may be an exception here.

    Third,

    Never put data Tables in your front end. Only construct as needed and destroy when finished with your load to the back end.

    Fourth,

    If everyone has their own FE as they should then having one person doing a compact on close will only affect that copy of the FE and not the rest.

    Fifth,

    The problem with running a compact and Repair on Front Ends with multiple copies is one or more copies could be connected to the BE at the time of C&R and this can corrupt the db.


    Chris Ward


    • Edited by KCDW Friday, June 8, 2012 4:16 PM add text
    • Marked as answer by Avan_Madisen Monday, June 11, 2012 2:05 PM
    Friday, June 8, 2012 4:12 PM
  • Compact on Close is seldom (if ever) required. In actual fact, properly designed databases simply don't need to be compacted that often. Remember, too, that there's actually a very slight risk of corrupting the database when you compact it, so the more you compact, the higher the risk.

    That being said, if you do set Compact on Close for the front-end database, all that gets compacted is the front-end: the back-end is not impacted at all.

    Realistically, though, I think you'd be best off replacing the user's copy of the front-end with a new copy stored on the server rather than compacting it.


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)


    Monday, June 11, 2012 1:06 PM

All replies

  • First,

    You should only have to compact and repair just before distribution in almost all cases. yours is an exception due to the build it sounds like.

    Second,

    You will have much anguish with your db if you compact and repair on close in almost every case. Yours may be an exception here.

    Third,

    Never put data Tables in your front end. Only construct as needed and destroy when finished with your load to the back end.

    Fourth,

    If everyone has their own FE as they should then having one person doing a compact on close will only affect that copy of the FE and not the rest.

    Fifth,

    The problem with running a compact and Repair on Front Ends with multiple copies is one or more copies could be connected to the BE at the time of C&R and this can corrupt the db.


    Chris Ward


    • Edited by KCDW Friday, June 8, 2012 4:16 PM add text
    • Marked as answer by Avan_Madisen Monday, June 11, 2012 2:05 PM
    Friday, June 8, 2012 4:12 PM
  • Your only problem lies with the fact that everyone is sharing the front end. If you set up the application properly for mutli-users, each user would have his own copy of the front end on his local drive. if that was set to compact on close it would solve all your problems.

    And if there are a bunch of local tables with data being manipulated how are your users not stepping all over each other?


    Bill Mosca
    http://www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Proposed as answer by JoyinKS Friday, June 8, 2012 7:57 PM
    Friday, June 8, 2012 7:32 PM
  • Perhaps I didn't explain things well, let me run through a few things:

    We have just starting testing the new version of this database today, the old version was not split, this one is. The original one was increasing in file size by approximately 10-12mb per day, we were compacting every day and even after 2 months the post-compacted file size was only 11mb This is the main reason I want to have the database compacted frequently. Although I'm hoping I've set up the database in a way that won't result in huge growth, only find out through testing.

    KCDW - I'll make the changes to remove the local tables and have them as temporary tables as you suggest. That will remove the need to have the front end compact. Thanks for the advice, I get the impression from what you are saying that compacting a split database is more likely to corrupt the data then a non-split one.

    Bill - I think you misunderstand, all users do have their own copy of the front end saved on their desk top. This is where local tables become using sicne every user has their own set and manipulate things that way.

    Monday, June 11, 2012 8:04 AM
  • Compact on Close is seldom (if ever) required. In actual fact, properly designed databases simply don't need to be compacted that often. Remember, too, that there's actually a very slight risk of corrupting the database when you compact it, so the more you compact, the higher the risk.

    That being said, if you do set Compact on Close for the front-end database, all that gets compacted is the front-end: the back-end is not impacted at all.

    Realistically, though, I think you'd be best off replacing the user's copy of the front-end with a new copy stored on the server rather than compacting it.


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)


    Monday, June 11, 2012 1:06 PM
  • The original database I built needed constant compacting, building a complicated multi-user database as your first ever development is what you could call 'the deep end, with sharks, and then some!' so I think I bit off one hell of challenge when I accepted this project. So it's no wonder I had problems with it.

    I've been monitoring the file sizes today with our testing and while it hasn't had a very high workload the file size of the backend hasn't changed much, so I think I've built it right this time. However I am planning on removing the local tables from the front end, which will hopefully make it a bit smaller.

    Douglas, my experience is disagreeing with you one point. During the development, when closing the front end I have seen the lock files for each backend file (there are 3) appear and disappear one at a time from the folder while it is compacting and then the file sizes shrink marginally. So it does seem to be compacting the backend files when the 'last' user closes their front end.

    All in all, I think I'll be removing the compact on close, since the need of it was primarily for the local tables, and since I will be removing those I will therefore have no need to compact unless the database files start to grow, and that we can do separately.

    Thanks for you help.

    Monday, June 11, 2012 2:05 PM
  • Bill - I think you misunderstand, all users do have their own copy of the front end saved on their desk top. This is where local tables become using sicne every user has their own set and manipulate things that way.

    Thanks for the clarification.


    Bill Mosca
    http://www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, June 11, 2012 2:07 PM
  • Can't explain why you're seeing size variation during the compact of the front end, but Compact on Close definitely only works on the current database (i.e.: the front-end in this case)


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    Monday, June 11, 2012 2:38 PM