Results 1 to 13 of 13
  1. #1
    digitalsocal is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    6

    Frequent Corruption / Persistent Locking Files in Network Environment

    I'm a relative Access newbie that recently inherited an access database that was built in 2005. It is stored on a networked location at our corporate headquarters in PA (I'm in CA). At the time, the database was extremely bloated and inaccessible from across the country, but seemed to work for the local users OK. Since we needed to access the data from CA, I split, updated, and compacted the database into a much more manageable size, and it has been working pretty well for about a year.

    The issue I have now is that often times, although users have insisted they are out of the database (and even verified through their task managers that no instances of msaccess.exe are running), the locking files won't go away, and I am unable to maintain or work on the database exclusively. This has also coincided with more frequent instances of "Unrecognized database format" and "Access has determined that your database is in an inconsistent state" type errors.

    I'm wondering if any of you have had similar issues or what the potential causes may be. We have already investigated whether all users have delete permissions on the server, and they do.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,845
    to delete the locking files, when all else fails, the server needs to be rebooted. With regards 'working on the database' the db is split so as far as the backend goes, you are presumably talking about making changes to the table designs. If you are talking about the front end and changes to forms/reports, each user should have their own copy of the front end on their own machine so should not be an issue. If users are all sharing the same front end, that will be where your problem is and leads to corruption

  3. #3
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    See here for splitting the database: https://support.office.com/en-us/art...3-51b1d73498cc

  4. #4
    digitalsocal is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    6
    Quote Originally Posted by Ajax View Post
    to delete the locking files, when all else fails, the server needs to be rebooted. With regards 'working on the database' the db is split so as far as the backend goes, you are presumably talking about making changes to the table designs. If you are talking about the front end and changes to forms/reports, each user should have their own copy of the front end on their own machine so should not be an issue. If users are all sharing the same front end, that will be where your problem is and leads to corruption
    Yes, well mainly I mean compacting the back-end, which I would like to do on a regular basis due to the bloat that was experienced before. We can have IT close the connections from their side, but was hoping to find out what may cause this without having to resort to them. I work for a very large company and getting IT's attention in a timely manner is close to impossible.

    Quote Originally Posted by JoeM
    See here for splitting the database: https://support.office.com/en-us/art...3-51b1d73498cc
    Thank you, but the db is already split

  5. #5
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The lock file tells you which user has it locked, get them to delete the task in Task Manager?

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,845
    locking files can remain open if users do not make an orderly exist from the front end (e.g. loss of connection, power failure). Note users should not be connecting wirelessly since wireless operation can play havoc with file locking procedures with temporary outages. What you can try to do is delete the locking file, if it deletes ok then you are free to go in and compact the backend. Also consider modifying your login routines to track user activity - do you get users with two logins and no logout record between them?

    And you haven't answered the question about users having their own copy of the front end

  7. #7
    digitalsocal is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    6
    Quote Originally Posted by Ajax View Post
    locking files can remain open if users do not make an orderly exist from the front end (e.g. loss of connection, power failure). Note users should not be connecting wirelessly since wireless operation can play havoc with file locking procedures with temporary outages. What you can try to do is delete the locking file, if it deletes ok then you are free to go in and compact the backend. Also consider modifying your login routines to track user activity - do you get users with two logins and no logout record between them?

    And you haven't answered the question about users having their own copy of the front end
    Yes, as far as I know all users are accessing the file from a hardline connection. I cannot delete the locking file without IT intervention because it always indicates it is open in Access, even though all users have assured me they are out. That, however, is impossible to verify, since I can't go checking on the computers because I am across the country.

    We do frequently see one user with more than one record in the locking file, and yes, each user has their own copy of the front-end database.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,845
    in that case, all I can suggest is
    Also consider modifying your login routines to track user activity - do you get users with two logins and no logout record between them?
    to see if you can narrow the problem down a bit, in the meantime you will have to rely on IT intervention.

    Other thought is you don't (or users don't) have excel opening access and not closing it properly?

  9. #9
    digitalsocal is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    6
    They do run a Word mail merge off of one of the tables, so I'll investigate that possibility. Could you elaborate on the modification of login routines?

    I did find out that there was a copy of the front end stored on the network and intended for backup / development use only that at least one user has been accessing to log into the database, and if more than one person has gotten in there simultaneously, it could be an issue. Went and removed the file this morning, and lo and behold, it was replaced (by that same user). So it looks like I have some training to do at least.

    I greatly appreciate your help!

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,845
    modification to login routines.

    I'm assuming users login already so you have a 'user table'. You will need an additional 'session log' table so whenever the user logs in a new record is appended with their userid and their date/time of login (can be a timestamp field with a default of now()). When they close the db, you need to run an event on your navigation form close event (or any form that serves the same purpose - open for the length of the session) to append another record with the date/time of logout. table would be something like

    tblSessionlogs
    logPK autonumber
    userFK long - link to user table
    login yes/no (true means login, false means logout)
    loginDT date/time default=now()

    if you don't have a user login table use

    environ("username")

    to get their windows login ID and use in place of userFK - and some code in the navigation form open event to populate login time instead of a normal login process

    If users just have access to tables, queries forms etc through the navigation window with no navigation form, guess you won't be able to do this

  11. #11
    digitalsocal is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    6
    Thanks! I'll give that a shot.

  12. #12
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It is stored on a networked location at our corporate headquarters in PA (I'm in CA).
    What is the BE? Access or SQL Server???
    Are you and/or the users connecting using the WAN or VPN or.... ????

    You should really read this about Access and WANs:
    http://www.kallal.ca/Wan/Wans.html

    Read carefully after the header "This network story gets worse!".

  13. #13
    digitalsocal is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    6
    Quote Originally Posted by ssanfu View Post
    What is the BE? Access or SQL Server???
    Are you and/or the users connecting using the WAN or VPN or.... ????

    You should really read this about Access and WANs:
    http://www.kallal.ca/Wan/Wans.html

    Read carefully after the header "This network story gets worse!".
    The majority of users connect via LAN, but we do have several connecting over WAN and VPN.

    That's an very insightful post. I don't know much of our specific network situation, but I've been throwing around the idea of moving to SQL server, and I think this line of research will gain me some much needed backup. Thank you!

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

Similar Threads

  1. Multi users in a network environment
    By sunnyday in forum Database Design
    Replies: 24
    Last Post: 03-09-2015, 09:40 AM
  2. Persistent .ldb
    By knarfreppep in forum Programming
    Replies: 1
    Last Post: 02-11-2015, 03:32 AM
  3. Replies: 23
    Last Post: 05-18-2012, 08:31 AM
  4. Record Locking Multi User Environment
    By praetorianprefect in forum Database Design
    Replies: 5
    Last Post: 04-16-2012, 02:31 PM
  5. Need Frequent Excel Imports with Primary Keys
    By UMassEngineer in forum Import/Export Data
    Replies: 1
    Last Post: 03-27-2011, 10:07 AM

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