Back End Read-Only Issue (2 Viewers)

[thomas.dickens]

Hi there

New to the forum. I am by no means a programmer, but I have built a couple of databases for my work and have a reasonable grip on the fundamentals. Most of it is self taught though, meaning I could easily have some bad habits.

I am having a massive issue with a database that I have built for one of our departments. Put simply, a record-locking file keeps getting 'stuck', forcing the database into a read-only format. This manifests in a couple of ways - either the user will all of a sudden see 'NAME?' instead of data on a datasheet, or when a user tries to open one of the available forms from the main menu (launching vba code that runs a series of queries followed by a form, including notably 1+ update queries), they will get an error message suggesting the action isnt possible. During this time, if I try to open the back end database, it can will automatically open in read-only format.

It has been hard to track down the cause of this issue, but it usually seems to be when either one of the front end files has crashes and the user has had to force close the database, or it could also have been caused when a user has closed their laptop/logged off without closing the database properly.

When looking at the back end file, I can see there is a record locking file. I have tried to delete this from my computer (making sure no users are in the database) and the users' computers. the file seems to disappear but it will return once you refresh file explorer.

I do not have specifics, but in almost every instance, the file will remain in read-only format until we return to the office the next day, where it seems to have sorted itself out. This leads to a lot of down time, and the issue seems to be cropping up more and more regularly.

I am wondering if anyone has experience with this sort of issue. I have read that this may be an issue with Access DBs with a BE that sits on the network, and that migrating to a SQL server would improve things. I have no experience with this, but looks quite doable. The only issue is our IT department (which I would need for downloading the relevant programs) claims to not be available for the next few weeks and that is too slow a turn around time.

Database details: the database is split, with the back end being held in a shared network folder. Each user has their own front end file. These were initially kept on the same network with no issue. I more recently had the users save a copy of their file to their desktop (as seems to be best practice) but the instances of the above issue seemed to get worse, so I have actually reverted to the users accessing their front end file from the network. As far as I can tell, each user has the necessary permissions to modify, read/write etc the files and the folders they are in. EDIT: there could be up to 5 users but in practice there are usually max 3 simultaneous users.

Any thoughts would be much appreciated!! I am hoping there is a fix I can implement myself without yet having to migrate to sql server.

 

[arnelgp]

Grant Full Access right to user Everyone on your backend folder to prevent being read-only.
you should also Split your db, the BE should reside to the Shared folder and distribute the FE to your users.
don't worry of the Locking files, ms access will take care of it's deletion.

 

[The_Doc_Man]

This is not uncommon. Unfortunately, it requires a certain level of discipline in using the DB.

The underlying cause is the Windows File Locking system. ANY TIME you are going to open a file - to read it or change it - you take out a lock. Locks are dynamic. You can take out a "write lock" because you want to update the file, but if someone else has either an exclusive lock or another write lock (that came in before your lock), you cannot perform the action. The good news is that locks, when processed, are usually processed very quickly, and get promoted or demoted quickly.

Which leads us to the .LACCDB or .LDB files (depending on the format of the BE file.) Access users each take out WINDOWS locks on the "ACCESS lock" file because they are adding - or removing - their names, or they are updating the section of the file that is locked. This prevents chaos and destructive interference that destroys the file's validity. BUT... there is an assumption, that you will release a file lock when you are done. When the file lock cannot be released because its owner app crashed, or the app's host lost power, or the owner left something open for modification (like a form that updates a record) and just walked away, you might have an Access lock file for which the pending WINDOWS lock is still open on the BE host. That "dangling" lock cannot be immediately deleted because that is how the Windows File System handles cooperative locking. You noted that the problem resolves itself overnight. That is because as a fail-safe, Windows will time-out its file locks. Once any blocking file lock has timed out, you can delete the lock file.

Unfortunately, there is no solution short of a barbed-wire bullwhip to get people to not be so careless with important resources. If the PC hosting the BE file is a Windows Server, the admin of that machine can kill the file locks on that file with a command-line UNLOCK command or can kill the dangling connection with appropriate network command line options. You can put timer routines in your app so that an "abandoned" app will self-destruct in an orderly manner after X minutes of doing nothing, though site policies can lock the session and make that impossible as well.

One strategy that SOMETIMES worked was to open the Access lock file read-only using Notepad, because the IP addresses of the locking machines will be visible in clear-text there. The usernames might or might not be shown. (Sometimes they are shown as SYSTEM, which is the owner of a remote network session, depending on the domain setup.) If you can identify the IP, you can identify the machine. Then you can go to the machine and possibly take action, or pester the machine's user to take proper action to close the Access session. Doesn't ALWAYS work but it can help for simple cases.

IF everyone has at least MODIFY rights (if not FULL ACCESS like Arnel suggests), then you won't get caught on a permissions problem. Access WILL take care of lock resolution - if the problem wasn't that the Access app exited abnormally. You should note that when you attempt to open your Access app but the Access lock file "update" lock cannot be established, Access.EXE will open the BE in READ-ONLY mode to protect against that destructive interference. Note that proper permission layout for a shared Access file is that the users in question must have MODIFY permission on the folder containing the app files as well as MODIFY for the data/app files themselves. AND everyone should have a private copy of the FE file on their machine, with FULL ACCESS to the individual FE files. Also, the individual FE files must be set up to NOT open the app in Exclusive mode, because that governs the file locks that are the basis of this problem.

 

[thomas.dickens]

Hi arnelgp

Thanks for getting back to me. I believe all users are in the same permission group and have: modify, read & execute, read and write. 'full control' will not be provided by our IT team because they reserve that sort of permission for themselves, but by understanding is that the above list should cover everything needed.

My assumption is that this isn't a permission issue as the users can use the app as intended for quite a while, and this only arises when there has been something like a crash (without permissions having changed at all)

 

[GPGeorge]

Hi arnelgp

Spoiler:

Thanks for getting back to me. I believe all users are in the same permission group and have: modify, read & execute, read and write. 'full control' will not be provided by our IT team because they reserve that sort of permission for themselves, but by understanding is that the above list should cover everything needed.

My assumption is that this isn't a permission issue as the users can use the app as intended for quite a while, and

this only arises when there has been something like a crash (without permissions having changed at all)

Reading previous responses along with this observation, I'd like to call your attention to what appears to be the primary cause of the problem. "Crashing" the database application. That will invariably leave a dangling lock file and can cause other problems for your users.

Your strategy needs to be two parts, I think.

1. The most difficult, perhaps. If crashes are being caused by undisciplined users, you primarily have recourse to better training. Perhaps a second line of defense would be instituting a method that kicks idle users out of the database after a suitable period. Maybe 30 minutes would be appropriate, or longer or shorter periods depending on your situation. Search this forum and other places on the internet for code that kicks idle connections out of the database. There have been many examples posted online over the years.
2. The long-term strategy. Figure out why your database application crashes at an increasingly frequent pace. That suggests some sort of internal problem, perhaps unhandled errors in code, or data loads that have become increasingly heavy with years of use. If it's not being caused by irresponsible users, stress testing of your database might surface some internal problem or problems you can address.

Unfortunately, network related issues can't easily be diagnosed without directly being connected, so we're left suggesting things you could try yourself.

Best of luck.

 

[thomas.dickens]

Hi The Doc Man

Thanks for the thorough response.

It has been hard to pinpoint the exact catalyst each time it has occurred, but it is at least reassuring to know that this sort of thing can occur either when the front end crashes, or when a user leaves the database open whilst logging off/closing laptop. Means I am on the right track.

If the PC hosting the BE file is a Windows Server, the admin of that machine can kill the file locks on that file with a command-line UNLOCK command or can kill the dangling connection with appropriate network command line options.

I'm not v familiar with this sort of thing but our IT team should in theory have the administrative rights. Funnily enough in one of these instances they were also unable to delete the record-locking file (but maybe they were not using your command suggestions. In any case, I cannot reliably get hold of them so I need a fix that does not involve going to them each time)

You can put timer routines in your app so that an "abandoned" app will self-destruct in an orderly manner after X minutes of doing nothing

This is interesting - I was looking into vba that could do this type of thing but because I assumed that the issue was catalysed at either: the exact moment access crashes OR the exact moment a user closes the laptop without closing the database (i.e. an acute issue rather than an ongoing one) I sort of assumed this would not work. I will look at some vba that will do as you are saying, though I might try to introduce the opportunity for the user to cancel.

QUESTION - I have tried to tell users that if their front end file stops responding, rather than forcing an end, just to leave it for a while to see if it starts responding. In your experience could this lead to fewer 'crashes' that could cause this current issue? or typically if the FE stops responding, the damage is already done?

One strategy that SOMETIMES worked was to open the Access lock file read-only using Notepad

Tried this but getting an error message saying I dont have permission to open the file and to contact the owner or admin to obtain permission.

Thanks again!!

 

[GPGeorge]

Ah, if the problem is that undisciplined users shut down their laptop without first closing any open applications on it, the only solution I can think of is a bigger stick, figuratively, of course. A mandatory training session could help. Passive aggressive behavior, unfortunately, is hard to change.

 

[thomas.dickens]

Thanks GPGeorge. Will definitely have a think about further training and encouraging good practice.

I cannot say for sure that it is just user discipline. It seems just as likely that unexpected crashes are causing this.



Can I just check something - in the majority of cases where this has happened, the users are still available to try to access the database. So the initial issue was caused or catalysed by one of their computers, causing ALL of them are having the read-only issues. However, rather than this being an issue where a user could technically go on and close the file/delete the record locking file, it seems to be something that is outside of anyone's control (until the next day when it has 'timed out'). This points more towards something being stuck than users simply forgetting to close down properly.

Does the above change any of the advice given so far?

Further - can anyone confirm whether migrating the back end onto a SQL server would improve performance in this area? I appreciate it would not fix whatever issues there are with the front end that may be causing crashes, but presumably it would handle these record locking events more robustly?

 

[GPGeorge]

Thanks GPGeorge. Will definitely have a think about further training and encouraging good practice.

I cannot say for sure that it is just user discipline. It seems just as likely that unexpected crashes are causing this.



Can I just check something - in the majority of cases where this has happened, the users are still available to try to access the database. So the initial issue was caused or catalysed by one of their computers, causing ALL of them are having the read-only issues. However, rather than this being an issue where a user could technically go on and close the file/delete the record locking file, it seems to be something that is outside of anyone's control (until the next day when it has 'timed out'). This points more towards something being stuck than users simply forgetting to close down properly.

Does the above change any of the advice given so far?

Further - can anyone confirm whether migrating the back end onto a SQL server would improve performance in this area? I appreciate it would not fix whatever issues there are with the front end that may be causing crashes, but presumably it would handle these record locking events more robustly?

Well, the crash is locking the database, so it makes sense that all are impacted the same.

I qualified my comment about the problem by saying "if" it is caused by shutdown of a laptop. So that's not a definitive consideration.

Rather, if a process is being interrupted during a write operation to the back end, during which it would be locked, that's another thing that could be going on.

And that brings up another possibility. Bad or failing network connection, or connections.

 

[The_Doc_Man]

Tried this but getting an error message saying I dont have permission to open the file and to contact the owner or admin to obtain permission.

This is an immediate issue. If you don't have permission to open the Access-lock file, you will ALWAYS get "exclusive" use of the BE file or will get rejected from ANY use (if someone else has exclusive use already.)

The folder where the BE file is kept MUST allow MODIFY for all DB users for the folder file itself and it must ALSO allow (inhereted) MODIFY for the BE and any other files kept therein. It is the nature of the Access beast that the Access lock file will automatically be in the same folder as the BE file, with the same name but the .LACCDB or .LDB file type depending on DB format. If you are a normal user of this app, you MUST have the ability to delete it, because that is the normal way it cleans up after itself.

The normal file-lock flow of that file is, the first user of the morning opens the BE and thus creates the Access-lock file. During shared use, other users open it and close it as their sessions open and close. If the last user of the Access-lock file closes it, that user deletes the file. Which is why the inheritance of MODIFY from the folder is crucial. Since the file is created and destroyed as a normal part of its use, it is not enough to grant MODIFY on that file directly. The moment you delete the file, it doesn't exist so has no permission, and the next user will create it again - if possible. You must allow that file to INHERIT the MODIFY permissions from the parent folder because of its dynamic nature.

 

[CJ_London]

How are the users opening their front end? By selecting the file? a shortcut? something else?

When you say you have put the FE back on the server, how do you ensure they open their copy and not someone else's? (generally a bad idea to have them on the server anyway - impacts performance among other things)

Has every user got the right settings in options? File>options>client settings - in particular the default open mode and record locking?

that migrating to a SQL server would improve things

In respect of FE's crashing, should be better, in respect of performance, probably worse - it depends on how good your data handling is - see this link

addressing performance issues

As your db expands and there are more active users, it is quite possible you will experience performance issues and may be considering upsizing to SQL Server or MySQL. Before considering such a transition, you should review your current design: Overview From a performance perspective, Access...

www.access-programmers.co.uk

I do think you need to investigate more about why it is crashing - what you are seeing may be a symptom, not the cause. Does it occur for one or more specific forms or actions? Are the users connecting to the BE over wifi or hard wired? If hard wired, do they disconnect to take their laptop into a meeting?

 

[thomas.dickens]

The folder where the BE file is kept MUST allow MODIFY for all DB users for the folder file itself and it must ALSO allow (inhereted) MODIFY for the BE and any other files kept therein. It is the nature of the Access beast that the Access lock file will automatically be in the same folder as the BE file, with the same name but the .LACCDB or .LDB file type depending on DB format. If you are a normal user of this app, you MUST have the ability to delete it, because that is the normal way it cleans up after itself.

Thanks - I have checked and all users have the same permissions, including modify.

How are the users opening their front end? By selecting the file? a shortcut? something else?

When you say you have put the FE back on the server, how do you ensure they open their copy and not someone else's? (generally a bad idea to have them on the server anyway - impacts performance among other things)

Firstly, thank you for the response! I create an ACCDE file from the central front end file. Until recently, I would then ask users to copy and paste that file onto their desktops. I have since moved back to having individually named ACCDE files on the network because this is how I had it before, and it felt like we had had more issues since the change.

Now that the FE files are all on the network in the same folder, it is difficult to prevent users accessing the wrong file. However whilst poor practice, this hasn't been the issue for the last few weeks as they have been accessing them from their desktops.

Has every user got the right settings in options? File>options>client settings - in particular the default open mode and record locking?

I will check this when I can. Checked on 1 of 3 main users so far and it was fine.

In respect of FE's crashing, should be better, in respect of performance, probably worse - it depends on how good your data handling is - see this link

Thank you! Will read through this.

I do think you need to investigate more about why it is crashing - what you are seeing may be a symptom, not the cause. Does it occur for one or more specific forms or actions? Are the users connecting to the BE over wifi or hard wired? If hard wired, do they disconnect to take their laptop into a meeting?

Yes good point. I have tried to diagnose best I can but the problem is that the users are not always in the same office as me and they aren't all tech-savvy so their idea of 'crashing' could vary from the database becoming unresponsive to just getting an error message (i.e. not a crash, and something that might well have arisen as a result of the record-locking issue, rather than a cause of it). As such I don't have a firm grasp on how often it is crashing - theoretically this has never caused the record locking issue.

I know for a fact that users have been closing their laptops without safely closing the database. Yesterday the record-locking issue arose following this (difficult to prove causation but the timing is somewhat conclusive). User closed laptop, went to a meeting, came back and the issue was present. All users were at that meeting so noone would have done anything to trigger it at the same time (other than also closing their laptop abruptly).

Users are connected via wifi. They report greater connectivity issues if they are working from home or working from a clinic, but not necessarily greater instances of the record locking issue (hard to say).


Again - thanks all of the help. Our IT department has suggested moving the database to somewhere higher up the directory (because we have a crazy and large set of files for our company, and the database is dug deep in there with lots of folders named things like '1. department A'). I will test this once I am back from annual leave.

 

[arnelgp]

Again - thanks all of the help. Our IT department has suggested moving the database to somewhere higher up the directory (because we have a crazy and large set of files for our company, and the database is dug deep in there with lots of folders named things like '1. department A'). I will test this once I am back from annual leave

that is another, No, No.
as much as possible the shared folder should only be 1 folder depth.

 

[CJ_London]

Users are connected via wifi. They report greater connectivity issues if they are working from home or working from a clinic, but not necessarily greater instances of the record locking issue (hard to say).

That is also a no no. Wi-Fi can drop connections then reconnect but the FE will lose track of the BE. Worse when working from home since you are dealing with a router as well. It is probably the reason for your issue.

If it has to be WiFi and users work from home I suggest review using terminal server. Alternatively look to significantly improve the quality and capacity of the WiFi and for home users, install a VPN


There was a thread recently about monitoring the connection to see if it had been lost. Can’t remember what the outcome was. I’ll see if I can find it

 

[RonPaii]

Error reports from users are notoriously un-reliable. Years ago I added logging of all trapped errors. Now I can check the log whenever there is a "CRASH" making it much easer to diagnose what the use(s) were doing before the problem.

 

[Pat Hartman]

but by understanding is that the above list should cover everything needed.

No. They need delete permission also but only to the lock file. Otherwise, the lock file never gets deleted.

 

[The_Doc_Man]

Now that the FE files are all on the network in the same folder, it is difficult to prevent users accessing the wrong file. However whilst poor practice, this hasn't been the issue for the last few weeks as they have been accessing them from their desktops.

This IMMEDIATELY needs clarification. NOBODY should EVER open the app FE from the common folder. Each user seriously needs to have a copy of the FE file in any convenient folder on their assigned workstation PC. Since the file is copied to their PC, they own the copy (in the Windows file ownership sense) and that copy will allow FULL ACCESS (to the FE). Because it is a private copy, there will be no actual file sharing under way, unlike what happens to the BE file - or to the FE file when users open it remotely.

In the "distributed FE" case, TECHNICALLY there are file locks because all opened files have Windows file locks. But there will be no contention for the locks of that file because everyone else would be using THEIR (different) private copies as well. Therefore, no lock "collisions" for the FE file... ever. There is also the issue of speed, since a local copy of the FE file operates at local disk speed, but the shared common file would run at network speed, which is usually slower because of the handshakes between the client and host.

The danger to the FE file, if physically shared, is that Windows enforces process isolation (as a security thing) and therefore, if you have two users sharing the physical FE file that is the common version of that file, anything that anyone does to store something in the app file runs a HUGE risk of overwriting something done by someone else. Because neither user task is permitted to know what the other users are doing except via any file lock mechanism. And this "left hand not knowing what the right hand is doing" situation is one of the mechanisms by which databases get badly corrupted.

 

[CJ_London]

OP is not saying users share a FE, they each have their own copy - but all those copies are the same folder and there is nothing to prevent them opening the ‘wrong’ copy