Results 1 to 6 of 6
  1. #1
    data808 is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Aug 2012
    Posts
    487

    Backend File Compact And Repair

    I have a multi-user database. The amount of users can vary but they are on average about 12 - 18 users at a time. I was wondering if I would be able to do a compact and repair on the backend file while the users are using the frontend files. I also wonder if I even need to do this in the first place. If I don't then that's even better. Please let me know any information on this.



    Thanks.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,419
    Let the db admin do it.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,025

  4. #4
    Rawb is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    Technically, as long as none of the Objects in the database are open (Tables, etc), then you can run a compact & repair. I'd be extremely cautious however. In the best case, the users will be locked out of the db for the duration of the compact & repair process and, if done over a network, you could actually corrupt your database.

    The best thing to do is to have a regular, scheduled time when you run it, say once every couple of weeks (or less). And when you do run it, it's best to follow these steps:

    1. Make sure nobody is using the database. The easiest way to do this is to wait until after hours (if possible) or set aside a scheduled time when everyone knows not to touch it.
    2. Cut off user access to the database and copy it off the server and onto your local machine.
    3. Make a backup of the database just in case anything goes wrong.
    4. Run the compact & repair on your local copy.
    5. Verify that the database is still useable.
    6. Copy the compacted database back to the server and give users their access back.

    Note on #2: You need to cut off access so that nobody adds anything to the copy on the server while you're running the compact & repair on your local machine. If they do, that data will be lost when you replace it with the compacted version.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,025
    A compact and repair is usually done to recover space used by Access. When you create temporary queries /forms/reports/ tables etc ; manipulate them; delete them; edit/change and re-save them, those deleted/changes things take up space. A compact and repair recovers such space. But this would not normally happen in the back end -which is why I asked the question in previous post.
    Editing/creating/modifying happens most during development where you are trying and testing things. If your production application is creating,using and then deleting some interim/temporary tables, then you could have a lot of "wasted space" and would require compact and repair.

    I would schedule the compact and repair for a time when there were no users on the application. There are procedures to "kick users out of BE" when necessary maintenance is to be performed.

    Good luck.

  6. #6
    data808 is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Aug 2012
    Posts
    487
    Thanks for all the help guys. You gave me the answers I was anticipating and will take the necessary steps to follow through with this procedure if need be. As far as I can tell and the explanation that Orange gave, the back end should be fine. I always do a compact and repair on the frontend file before I release it to the users.

    In this case my question for a compact and repair for the backend was based on pure curiosity and that I did not want any unpleasant surprises in the future. So far since I released my database last year, I still have not done a compact and repair for the backend and everything is still working ok. I was just concerned if it needed to be done or not. Thanks for the help guys.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 12-09-2014, 09:40 AM
  2. Compact & Repair Code
    By aamer in forum Access
    Replies: 6
    Last Post: 11-04-2014, 03:51 PM
  3. Compact and repair
    By data808 in forum Access
    Replies: 4
    Last Post: 03-30-2014, 03:22 AM
  4. Compact and Repair
    By Cyberice in forum Access
    Replies: 3
    Last Post: 03-18-2014, 03:22 PM
  5. Compact & Repair, Will I Run Into Problems?
    By robsworld78 in forum Access
    Replies: 1
    Last Post: 01-10-2012, 05:11 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums