Permission Denied on move or delete for last file in a folder

Question

I have an MS Access VBA procedure inside a module that has a for each loop on files in a particular folder.

Inside the loop, I create a new file in a separate folder that is then filled with cleaned data from the file in the loop. I import the new file's data into a SQL Server 2005 database. Upon successful import, the script deletes the cleaned file and attempts to move (or copy/delete) the file in the loop to an archive subfolder in the same directory.

The file looping works fine on all files in the looped folder... except for the last file. That's when the Permission Denied error pops up.

The error message, under the above-mentioned circumstance, always happens on one of the following (I've tried several, similar commands):

fso.MoveFile
fso.DeleteFile (just after copy)
f.Move
f.Delete (just after copy)
Name origin As destination

Here is my code:

Dim fso As New Scripting.FileSystemObject
Dim f As file
Dim processingFiles As Files

If fso.FolderExists(incomingPath) Then
    Set processingFiles = fso.GetFolder(incomingPath).Files
End If

For Each f In processingFiles

    /*this is where the create a new file and clean it part runs - works fine*/

    If fso.FileExists(archivePathFile) Then
        Kill archivePathFile
    End If

    If fso.FileExists(tempPath & "\cleaned_processing_file.txt") Then
        Kill tempPath & "\clean_processing_file.txt"
    End If


    f.Move archivePathFile   '<------------- Permission Denied, last file in folder
    Debug.Print f.Name & " is now in " & incomingPath & "\Archive"

    'f.Copy archivePathFile, True 
    'f.Delete True '<----------------------- Permission Denied, last file

    'Name origPathFile As archivePathFile '< Path/File access error, last file

Next '<--- For Each Loop

Does your `processingFiles` collection include any system files like `desktop.ini` that could be throwing a wrench into the works? — Gord Thompson, Mar 15 '13 at 18:11
It contains only .txt files. The only subfolder is its archive folder. Read only is turned off on all files and folders (including processingFiles). I can move thousands of files to the archive subfolder without issue; it is simply the last file that gives me trouble. — coge.soft, Mar 15 '13 at 19:36
I suppose you've tried `debug.print f.Name` before each move/delete to be sure it's not a hidden file that's causing the problem. You should insert the line `DoEvents` after the SQL import to allow SQL a chance to operate, but this doesn't explain why it's always the last file that causes the problem. — grahamj42, Mar 15 '13 at 20:10
I appreciate the `DoEvents` tip, @grahamj42. I learned about that trick the hard way. Yes, I had `Debug.Print "File Name is: " & f.Name` - just below `DoEvents`, in fact. I can confirm it is always the last file in the collection of files. I even put a counter to Exit For on the first 2 files for testing purposes... the 2nd file would fail (Permission Denied). I then tried all 25,000 files... the last file would fail (Permission Denied). — coge.soft, Mar 15 '13 at 20:25
You say *"I even put a counter to Exit For on the first 2 files for testing purposes... the 2nd file would fail"* but if you've already exited the loop, are you doing something with f after the loop exits ? — grahamj42, Mar 15 '13 at 20:34
@grahamj42 thanks for your help. I was able to get it to work; in a round-about way. — coge.soft, Mar 18 '13 at 14:51

score 1 · Accepted Answer · answered Mar 15 '13 at 22:53

I'm posting this as an "answer" so the code will show up, but you (obviously) don't have to accept it...

I just tested the minimal case...

Sub move_to_foo()
Dim fso As New FileSystemObject
Dim processingFiles As Files, f As File
Set processingFiles = fso.GetFolder("C:\__tmp").Files
For Each f In processingFiles
    Debug.Print "Moving """ & f.Name & """..."
    f.Move "C:\__tmp\foo\" & f.Name
Next
End Sub

...and it does not fail on the last file in the folder, so your problem has to be something specific to your code. If you choose to edit your question and provide more details then I'll be happy to help, but right now your question -- as stated -- has no answer.

Thank you for your hlpe. I couldn't get it the work the way it should. I can only guess that perhaps the utility that writes to the folder was causing some kind of lock. I made it work by copying the files into a temp folder, looping on that, and moving the live files to archive inside the loop. once outside the loop, I delete the files in the temp folder without the permission denied issue. — coge.soft, Mar 18 '13 at 14:51

score 1 · Answer 2 · edited Dec 15 '15 at 16:02

I had the exact same problem as you. All files would be moved from the source directory to the target directly except for the last one, which would cause "Permission Denied" to be displayed. This would happen if there were 2 files or 30 files. I was copying the data from the files to a SQL database before the files were moved as well. The solution is to set the objects to "Nothing" after they are used to parse the files and insert the SQL data.

For example, after the SQL script data is inserted, the files can be moved. Free up the object resources:

Set objFSO = Nothing
Set objTextFile = Nothing

Permission Denied on move or delete for last file in a folder

2 Answers2