Microsoft Access's Surprising Folder Locking Behavior

Folder In Use
The action can't be completed because the folder or a file in it is open in another program

Close the folder or file and try again.

This error message–and the various screenshots related to it–appear when you try to modify a folder that an application has a lock on.  So what does this have to do with Access?  Don't worry, we'll get there.

Folder/File Locking

In Windows, file locking is used to support concurrency at the file system level.  

A shared lock allows multiple processes to read a file or folder simultaneously, but prevents any process from modifying it until the lock is released.  

The "Current Directory" of Every Windows Application Always Gets Locked

This is easiest to demonstrate with cmd.exe.

With the cmd application, the current directory is the folder shown to the left of the ">" symbol at the command prompt.  For example, if the command prompt is C:\Users\Mike> then the current directory of the cmd application is "C:\Users\Mike".

The following steps will show you how setting and changing the current directory of the cmd.exe application impacts whether you can delete an empty folder.

1. Run cmd.exe
2. Type in mkdir TestFolder then press Enter to create a new subfolder
3. Type cd TestFolder to [c]hange the current [d]irectory to the TestFolder you just created
4. Open File Explorer in TestFolder's parent directory: explorer ..
5. Right-click the folder named "TestFolder" and choose Delete
6. Click [Cancel] on the "Folder In Use" dialog
7. In the cmd window, type cd .. to change the current directory to the TestFolder's parent directory
8. In File Explorer, right-click the folder named "TestFolder" and choose Delete; this time the folder is deleted

Every Windows Application MUST Define a Current Directory at All Times

This fact makes sense and is intuitive for command line applications like cmd and PowerShell, but it's true of every single Windows application.  To view the current directory for any Office application, including Microsoft Access, you can use the CurDir function.

The ChDrive and ChDir Methods Allow You to Explicitly Set Access's Current Directory

If you want to change the current directory in Access (or any Office application with VBA), you can do so using the "change directory" statement, ChDir.

You have to be careful if you want to set the current directory to a folder on a different drive, though.  That requires using the "change drive" statement, ChDrive, in addition to ChDir.

VBA will remember the current directory when you switch to a different drive, but it will not lock it.

Like All Windows Applications, Microsoft Access Locks Its Current Directory

Let's test the folder locking behavior of Access's current directory setting now.

We'll use the "make directory" statement, MkDir, and "remove directory" statement, RmDir, for demonstration purposes.

Now, let's use the ChDrive statement to change the current directory to a different drive.  Changing drives is not necessary, but I want to demonstrate that the deletion will proceed even though the current directory on the previous drive is still remembered.  If you delete the "remembered directory" of another drive, when you switch back to that drive using ChDrive, the current directory will be set to the root of the drive.

Observe:

Using the Application.FileDialog Object Changes Access's Current Directory

So far we've seen some things that you probably didn't expect.  Now, get ready to really have your mind blown.

If you call the Application.FileDialog object (which you can use to get easy access to the system's File Open dialog), then whatever folder the user navigates to becomes the new current directory.  

This is true even if the user clicks [Cancel] in the dialog!

Network Folders are also Affected

To make matters worse, this behavior extends to network folders.  

Thus, it is possible for one of your users to use the Application.FileDialog to navigate to a network folder, cancel out of the dialog, lock their computer, and go home for the weekend, effectively blocking all other users from renaming or deleting the folder.

Tools to Deal with Locked Folders

Handle

The best tool that I know of to deal with file and folder locks is the Handle Viewer utility, handle, from Mark Russinovich.

To use it, call the command line utility handle and pass it the name (or partial name) of a folder or file where you are receiving a "File or Folder In Use" message from Windows.  It will take several seconds, but eventually it will show all the locked files and folders that contain that name, along with information about the process(es) that have lock(s) on the file/folder:

This is the best tool to use if you know a file or folder is locked, but you don't know why.

Process Explorer

Another tool from Mark Russinovich, Process Explorer, is like Task Manager on steroids.  And not those designer Barry Bonds steroids, either.  I'm talking about full-on, Lyle Alzado-style anabolic steroids.

This is the best tool to use if you want to know which folder an application has locked by looking at its "Current Directory" setting in the Image tab of the process Properties window.

You can also search by folder/file name to identify the process responsible for locking a file or folder by going to Find > Find Handle or DLL... then searching for the folder or file name:

Acknowledgements

• First drafts of portions of this article's body generated with the help of ChatGPT