Built-in functionality to diagnose and analyze file lock issues in VBA is very limited. In this text I will share a story on issues caused by this and how I solved these issues.
Permission Denied – The Problem with File Locks
Error 70 – Permission denied
It happened twice this month. Two different customers reported an urgent issue with custom applications we built for them. A mission critical file processing feature stopped working out of the blue. “Error 70 – Permission Denied” was the simple, but not very comprehensible, error message.
At first, this sounded very much like an issue with file system permissions, but error analysis quickly ruled that out.
We then asked the user: “Is it possible that you’ve got the file you selected for processing still open in another application? – If yes, please close that application.”
Reply: “No, I didn’t open the file in another application.”
It turned out; this was the actual root cause of both issues. The files in question were Microsoft Excel and Microsoft Word files and the users opened them in Excel/Word to check the content, hours or even days before they tried to process the files with our custom application. They couldn’t remember this anymore and they did find these open files in the dozens of open windows on their computer.
Clear Communication Leads to Better Results
It’s easy to cry “Incompetence!” and blame the users for this. Agreed, when we particularly asked about this, they should have checked more thoroughly. However, the original message, “Permission Denied”, was just the raw error message passed on to the user and it was not helpful at all. That is on us. At least, we should have added something like “Check if the file is open in another application.” to this particular error message. – But that is what we asked the users when trying to resolve the issues and they still denied it. I doubt, the text in the error message would have made that much of a difference.
What if the message would have been: “The file is open in Microsoft Excel! Find the Excel window with this file and close it.” – I think this wording would have had a much better chance of success.
I wanted to provide better message for file locking issue for quite a while, but I didn’t find the time to do something about it.
The FileLockAnalyzer Component
Comprehensible file lock message
After these two support cases in shot succession, I finally decided to try to find a solution to show more informative message about issues with file locks to users of our applications.
I researched different options to solve these issues. Several of them turned out to be a dead end because they were either not suited for my scenario at all or appeared to be too difficult to implement in VBA. However, I discovered the Windows Restart Manager API, which provides the required functionality and seemed to be possible to implement in VBA.
I’ve done quite a bit of in-depth research and practice of Windows API code in my new video series on the Win32-API and VBA. With that base knowledge, it was possible to implement the desired functionality in VBA, even though the Restart Manager API includes some fairly complicated stuff, such as arrays of nested user defined types.
After I implemented the core functionality, I realized that it also is reasonably simple to wrap the implementation in two reusable VBA class module. As this is functionality, I haven’t seen anywhere else in a VBA usable implementation, I decided to make my class module available as a commercial component to all VBA developers.
The result is the FileLockAnalyzer component in our Access Dev Tools shop, which allows you to use the same functionality in your VBA applications quickly and with little integration effort.
Better Messages for File Lock Issues
Better Messages for File Lock Issues
Built-in functionality to diagnose and analyze file lock issues in VBA is very limited. In this text I will share a story on issues caused by this and how I solved these issues.
Permission Denied – The Problem with File Locks
It happened twice this month. Two different customers reported an urgent issue with custom applications we built for them. A mission critical file processing feature stopped working out of the blue. “Error 70 – Permission Denied” was the simple, but not very comprehensible, error message.
At first, this sounded very much like an issue with file system permissions, but error analysis quickly ruled that out.
We then asked the user: “Is it possible that you’ve got the file you selected for processing still open in another application? – If yes, please close that application.”
Reply: “No, I didn’t open the file in another application.”
It turned out; this was the actual root cause of both issues. The files in question were Microsoft Excel and Microsoft Word files and the users opened them in Excel/Word to check the content, hours or even days before they tried to process the files with our custom application. They couldn’t remember this anymore and they did find these open files in the dozens of open windows on their computer.
Clear Communication Leads to Better Results
It’s easy to cry “Incompetence!” and blame the users for this. Agreed, when we particularly asked about this, they should have checked more thoroughly. However, the original message, “Permission Denied”, was just the raw error message passed on to the user and it was not helpful at all. That is on us. At least, we should have added something like “Check if the file is open in another application.” to this particular error message. – But that is what we asked the users when trying to resolve the issues and they still denied it. I doubt, the text in the error message would have made that much of a difference.
What if the message would have been: “The file is open in Microsoft Excel! Find the Excel window with this file and close it.” – I think this wording would have had a much better chance of success.
I wanted to provide better message for file locking issue for quite a while, but I didn’t find the time to do something about it.
The FileLockAnalyzer Component
After these two support cases in shot succession, I finally decided to try to find a solution to show more informative message about issues with file locks to users of our applications.
I researched different options to solve these issues. Several of them turned out to be a dead end because they were either not suited for my scenario at all or appeared to be too difficult to implement in VBA. However, I discovered the Windows Restart Manager API, which provides the required functionality and seemed to be possible to implement in VBA.
I’ve done quite a bit of in-depth research and practice of Windows API code in my new video series on the Win32-API and VBA. With that base knowledge, it was possible to implement the desired functionality in VBA, even though the Restart Manager API includes some fairly complicated stuff, such as arrays of nested user defined types.
After I implemented the core functionality, I realized that it also is reasonably simple to wrap the implementation in two reusable VBA class module. As this is functionality, I haven’t seen anywhere else in a VBA usable implementation, I decided to make my class module available as a commercial component to all VBA developers.
The result is the FileLockAnalyzer component in our Access Dev Tools shop, which allows you to use the same functionality in your VBA applications quickly and with little integration effort.