Results 1 to 9 of 9

Thread: Maintenance of MS Access Back End

  1. #1
    Board Regular
    Join Date
    Oct 2008
    Location
    New York, NY
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Maintenance of MS Access Back End

    Hello~

    I've created and administer an Access DB application -- 20+ end users -- split db -- each user has a copy of an accde executable file on their desktop, while the back end is on a network server. The front end is programmed to shut down automatically @ 8pm every night.

    Once in awhile (every 3-ish weeks or so), the back end will "freeze" -- nobody is in the db, there is no lock file, but the database can only open read only, no record can be edited, and no record can be added. This only seems to happen first thing in the morning -- as people start their day and start to use the db. It has yet to happen in the middle of the day.

    This does seem to clear itself up if I give it a couple of hours, but of course the business cannot wait a couple of hours. When this happens, I make a copy of the back end, and re-issue a front end linked to this copy and make that our new back end.

    Is there a way to prevent this? I do compact and repair the back end once a week or so -- do i need to do that every day? I am not considering simply creating a new back end every week or so as a preventative measure. But is there something else that might be going on? Is it something in my coding that might be causing the back end to 'lock' like this?

  2. #2
    Board Regular
    Join Date
    May 2013
    Posts
    667
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Maintenance of MS Access Back End

    I cannot offer a reason to the problem, but they used to so this in a bank I worked in for Excel workbooks and it annoyed the hell out of me as I had placed the workbooks on my MRU list.

    Why when you make copy, why can you not rename the old to anything other than the real name, just a date added would do, and then rename the copied version to the correct backend name.?

    Then you would not need to muck around with new FE distribution.?

    Just a thought.?
    Office 2007
    Access novice. Sometimes trying to give something back

  3. #3
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,492
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Maintenance of MS Access Back End

    you might need to compact and repair more often if the db is expanding in size too much every day.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  4. #4
    Board Regular
    Join Date
    Oct 2008
    Location
    New York, NY
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Maintenance of MS Access Back End

    Thanks for the suggestion -- however when the Back End is in this 'locked' read-only state, it cannot be renamed -- I think we're going to go ahead and simply create a new back end every week as a preventative measure.



    Quote Originally Posted by welshgasman View Post
    I cannot offer a reason to the problem, but they used to so this in a bank I worked in for Excel workbooks and it annoyed the hell out of me as I had placed the workbooks on my MRU list.

    Why when you make copy, why can you not rename the old to anything other than the real name, just a date added would do, and then rename the copied version to the correct backend name.?

    Then you would not need to muck around with new FE distribution.?

    Just a thought.?

  5. #5
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,492
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Maintenance of MS Access Back End

    Just out of curiousity is there anything in your BE other than data tables? What is the file size of the BE? Also I guess FWIW do you expect in time to be increasing the number of users?
    Last edited by xenou; Sep 18th, 2019 at 05:35 PM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  6. #6
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,727
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Maintenance of MS Access Back End

    The front end is programmed to shut down automatically @ 8pm every night.
    That is something that raises a red flag. If anyone left a half-finished form edit in play and you're forcing a shut down you can expect things like this to happen. Not sure myself, but I would even worry about any connections that any open fe had to the be file when you terminate that fe even if there is no editing going on.
    Last edited by Micron; Sep 18th, 2019 at 09:18 PM. Reason: clarification
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,867
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Maintenance of MS Access Back End

    Are your front ends referencing your back end by way of mapped drives? If so, try using the full network path in your linking.

  8. #8
    Board Regular
    Join Date
    Oct 2008
    Location
    New York, NY
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Maintenance of MS Access Back End

    Thanks for all the suggestions -- I only have Tables in the Back End, and I do use the full network path in linking my tables. The point about shutting down automatically and the issues it might cause is something I'll have to think on a bit -- I have it shut down at 8pm as theoretically nobody will be working that late -- and I do have the fe create a persistent connection on open and it releases that connection on close. But I might play with the timing of this.

    In the mean time, I've simply replaced the front end every Saturday morning for the last 3 weeks and so far we haven't had any issues...

  9. #9
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,727
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Maintenance of MS Access Back End

    One other possibility comes to mind. One (or more) users or a process doesn't have the proper permissions on the folder, i.e. the db can be opened but the lock file cannot be created. This will force the db to be opened exclusively by that user or process, in which case you would not see a lock file as you report. Replacing the be would no doubt override this issue. However, the exclusivity would exist as long as the db was opened and I don't know what that could mean in your case. Thus the idea may not be valid, but I thought I'd throw it in here as a potential.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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