Frequent Corruption / Persistent Locking Files in Network Environment

[digitalsocal]

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.

[CJ_London]

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

[JoeM]

See here for splitting the database: https://support.office.com/en-us/art...3-51b1d73498cc

[digitalsocal]

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.

See here for splitting the database: https://support.office.com/en-us/art...3-51b1d73498cc

Thank you, but the db is already split

[aytee111]

The lock file tells you which user has it locked, get them to delete the task in Task Manager?

[CJ_London]

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

[digitalsocal]

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.

[CJ_London]

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?

[digitalsocal]

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!

[CJ_London]

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

[digitalsocal]

Thanks! I'll give that a shot.

[ssanfu]

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!".

[digitalsocal]

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!