Need VBA for Deleting files based on date modified and owner

[padinka]

In Access 97

I have 5 retention folders (Daily, Weekly, Monthly, Quarterly and Annual) for data storage. I need to be able to search through the folders and delete files bases on our retention schedules (Daily folder(1 week), Weekly folder(2 weeks), Monthly folder (3 Months) etc) for a specific owner. Can someone give me some sample code to do this???

 

[MichaelRed]

padinka.

Option Compare Database
Option Explicit

Type RetentionType
FileFolder As String 'path of folder
RetentionInterval As String ' 'w' for Week, "m" for month ...
NumIntervals As Integer '1 for daily; 2 for weekly, 3 for monthly ...
End Type

Function DeleteFiles()

Dim Retention(4) As RetentionType
Dim Idx As Integer
Dim FilIntvl As Integer
Dim MyFil As String
Dim FilStamp As Date

Retention(0).FileFolder = "C:\My Documents\" '"Your 'Daily' Path Here"
Retention(0).RetentionInterval = "ww"
Retention(0).NumIntervals = 1

Retention(1).FileFolder = "C:\My Documents\" '"Your 'Weekly' Path Here"
Retention(1).RetentionInterval = "ww"
Retention(1).NumIntervals = 2

Retention(2).FileFolder = "C:\My Documents\" '"Your 'Monthly' Path Here"
Retention(2).RetentionInterval = "m"
Retention(2).NumIntervals = 3

Retention(3).FileFolder = "C:\My Documents\" '"Your 'Quarterly' Path Here"
Retention(3).RetentionInterval = "m"
Retention(3).NumIntervals = 6

Retention(4).FileFolder = "C:\My Documents\" '"Your 'Annually' Path Here"
Retention(4).RetentionInterval = "m"
Retention(4).NumIntervals = 24

For Idx = 0 To UBound(Retention)
MyFil = Dir(Retention(Idx).FileFolder & "*.*&quot 'First File

While MyFil <> &quot;&quot;

If (MyFil = &quot;.&quot; Or MyFil = &quot;..&quot Then
'Current or parent directory
GoTo NxtFil 'Skip Directories!
End If

If ((GetAttr(Retention(Idx).FileFolder & MyFil) And vbDirectory) = vbDirectory) Then
'Named (Sub) directory
GoTo NxtFil 'Skip Directories!
End If

FilStamp = FileDateTime(Retention(Idx).FileFolder & MyFil) 'Date-Time of File Modification
FilIntvl = DateDiff(Retention(Idx).RetentionInterval, FilStamp, Now())

If (FilIntvl > Retention(Idx).NumIntervals) Then
'Kill (Retention(Idx).FileFolder & MyFil)
Debug.Print &quot;Old File: &quot; & Retention(Idx).FileFolder & MyFil & &quot; &quot; & FilStamp
End If
NxtFil:
MyFil = Dir
DoEvents 'Chance to Break and see the activity

Wend

Next Idx

End Function


I &quot;Commented Out&quot; the Kill Statement - I certainly do not want to have this actually happen on MY system. You probably want to 'check' it out before using it &quot;Live&quot;. To have it &quot;DO IT&quot;, un-comment the kill statement (and, possibly, comment out the debug.print statement. Also, the paths to YOUR folders/directories need to be set appropiatly.

[red]This CODE is HAZZARDOUS. DO NOT play with it. Do NOT run it without understanding it.[/red]

MichaelRed
There is never time to do it right but there is always time to do it over.

 

[padinka]

WOW. Great. But I need to only delete files where the file's creator is 'xxxx'. Is that possible? Thanks so much for your help on this....

 

[MichaelRed]

padinka,

I am not aware of any 'Author'/'Created by' attribute or property available to MS Access/VBA. This, obviously, exists in at least a limited fashion. You can 'see' the author & creation date info on &quot;Word&quot; Documents from Windows Explorer just as if it were a &quot;Tool Tip&quot; for a MS Access Control. But I do not know how this is done. Further, this is not a universally available property of the (Windows) file system, as the Author/creation information does not show up on HTML files - even ones I created w/ WORD!. If somone knows what this property is - and how to access it, then it would be possible to add that to the process - until/unless then, this is as far as I know how to help you.

MichaelRed
there is never time to do it right but there is always time to do it over

 

[harmmeijer]

If the owner is displayed when you give a dir command at the dos prompt (if not ignore the rest of this reaction) you can import the output form the dir (dos command) into your application.
I use this for access to get a quick list of all mp3 files.

Open &quot;c:\imprtaccesmp3files.bat&quot; For Output As #1
Print #1, &quot;@echo off&quot;
Print #1, &quot;echo If this screen stayes open you should right click the window at the top&quot;
Print #1, &quot;echo and choose properties - close on exit&quot;
Print #1, &quot;del c:\ok.ok&quot;
' you should change the options below to get the result youre looking for.
Print #1, &quot;dir &quot; & Me.path & &quot;\*.mp3 /A-D /O:N /S /B > C:\ACCIMP.TXT&quot;
Print #1, &quot;echo now the app may continue > c:\ok.ok&quot;' here a file ok.ok is beeing made on the c:\ to check if the batchfile is finished.
Close #1
Call Shell(&quot;c:\imprtaccesmp3files.bat&quot;, 1) ' note that the ,1 does not work in visual basic
' here a file ok.ok is beeing made on the c:\ to check if the batchfile is finished.
checkvar = Dir(&quot;c:\ok.ok&quot
Do While checkvar = &quot;&quot;
checkvar = Dir(&quot;c:\ok.ok&quot
Loop
Open &quot;c:\imprtaccesmp3files.bat&quot; For Output As #1
Print #1, &quot;@echo off&quot;
Print #1, &quot;echo If this screen stayes open you should right click the window at the top&quot;
Print #1, &quot;echo and choose properties - close on exit&quot;
Print #1, &quot;del c:\ok.ok&quot;
Close #1
Call Shell(&quot;c:\imprtaccesmp3files.bat&quot;, 1) ' note that the ,1 does not work in visual basic
' now you need to import the file called C:\ACCIMP.TXT
' make a file import profile (I only know how to make that in access)
' because the komma (,) is used in filenames you should make it fixed length (I guess)
' I do not have an NT machine so I do not know if you can display
' the owner of the file with a dir command in dos