Back End Read-Only Issue (1 Viewer)

[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.