This is one for the experts!
I have been tasked to clean up part of one of our network drives, which involves merging 5 directories into one, and deleting duplicate and unnecessary files.
I have put the whole drive directory into MS Access, and using queries and VBA I have identified and marked the files to be deleted.
All this works perfectly, and marks 16,800 / 54,000 files for deletion. Here is the procedure I am trying to use to do the deletions, using the VBA Kill statement:
Code:
Sub KillFiles()
Dim Filename As String
Dim db As Database, rst As Recordset
Dim SQL As String
On Error GoTo ErrProc
Set db = CurrentDb
SQL = "Select * from [All MOSART Files with Directories] where MarkForDeletion and not filedeleted and " & _
" left([directory Name],15) = 'R:\MOSART FINAL'"
Set rst = db.OpenRecordset(SQL)
While Not rst.EOF
Filename = Chr(34) & rst![directory name] & "\" & rst![file name] & Chr(34)
Debug.Print "Kill " & Filename
Kill Filename
rst.Edit
rst!filedeleted = True
rst.Update
rst.MoveNext
Wend
rst.Close
Set db = Nothing
Exit Sub
ErrProc:
Debug.Print "Error " & Err.Description & " when deleting " & vbCrLf & Filename
rst.Close
Set db = Nothing
End Sub
However, when I run it, the code fails on the first file, with a "File not found" error. I know the file name is correct, because the debug.print shows it correctly.
Here is where it gets odd: if in the immediate window I click on the line that debug.print has produced, (see bold below) and execute it, it works - the file is deleted.
kill "R:\MOSART FINAL JG\5555-106 Briefings & Conf\fsa25A.tmp" (this works in the immediate window)
A little test shows that the simplest of the simple:
Sub x()
Dim txtFilename As String
txtFilename = "R:\MOSART FINAL JG\5555-101 Budget\dir budget Qtr - 20 Jun 01.xls"
Kill txtFilename
End Sub
does work. I'm thinking it may be a timing issue, where I try the second Kill before the first one has completed. I put a Doevents after the Kill, but no luck.
Any ideas?
Thanks!
John