Find old files and delete them

[twinnyfo]

I currently have a database with a front end of just code and four back-ends with just data. I have several back ends to manage the size of the databases, as well as to group like data in one place, which makes it easier to manage (personal preference).

Whenever my FE opens, it checks to see the last time the BEs were backed up and if it has been more than six hours, it will make copies of the BEs and save them to a Backup folder. The DB also adds a time stamp to each copy, so that I know, just by looking at the file name, when the file was backed up. The file itself, may be older than 30 days, because not all of my BEs are accessed every day. Having it back up EVERY time the DB was opened would definitely cause things to quickly get out of hand.

However, as you can guess, backing up several BEs several times a day (ranging from 4-50 MB each) quickly causes a strain on storage space. Right now, I occasionally go into my BACKUP folder, find the older files (>30 days) and just delete them.

Here is what I want to do:

When the FE opens, have the DB check the list of files, find the date stamp, decode it, and if the file was backed up more than 30 days ago, delete it. This way, I will only have no more than 30 days' worth of BEs.

My challenge is, how do I tell the DB to go to a particular directory and go through a list of files? Then, is it possible for VBA to delete files from a directory?

Any assistance would be appreciated.

For reference, here is the code I use for my backup:

'Database Path constants in another module
Public Const strDBASEPATH As String = "\\ServerName\DATABASE\"
Public Const strBACKUPPATH As String = "\\ServerName\DATABASE\BACKUP\"

'Code within my start up form
If Me.txtLastBackup < Now - 0.25 Then
    Dim FSO
    Dim strDate As String
    strDate = Format(Date, "yyyy-mm-dd") & "-" & Format(Time, "hh-mm")
    Set FSO = CreateObject("Scripting.FileSystemObject")
    FSO.CopyFile strDBASEPATH & "DATA\DATA-BE1.accdb", strBACKUPPATH & "DATA-BE1 BACKUP - " & strDate & ".accdb"
    FSO.CopyFile strDBASEPATH & "DATA\DATA-BE2.accdb", strBACKUPPATH & "DATA-BE2 BACKUP - " & strDate & ".accdb"
    FSO.CopyFile strDBASEPATH & "DATA\DATA-BE3.accdb", strBACKUPPATH & "DATA-BE3 BACKUP - " & strDate & ".accdb"
    FSO.CopyFile strDBASEPATH & "DATA\DATA-BE4.accdb", strBACKUPPATH & "DATA-BE4 BACKUP - " & strDate & ".accdb"
    Me.txtLastBackup = Now
End If

[NeoPa]

It's all possible.

Use Dir() to identify all the matching files in the folder. Parse the filename for each when you have it (in your loop). If the filename reflects a date earlier than 30 days ago then delete it using the Kill statement.

The question is too broad of scope simply to provide a solution, but if you work on providing one from these pointers then we can probably help with particular details if you struggle anywhere along the way.

PS. This can be done with the File System Objects library (FSO), but it can also be done quite simply using just VBA and the inbuilt functions provided.

[Mariostg]

If it might help a bit on your space issue, you could use the method DBEngine.Compac tDatabase as part of your backup routine.

[NeoPa]

There's little point in doing that in code Mario. Access has an inbuilt option for databases to Compact and Repair automatically on close. Check out the web for various comments as to the advisability of this approach, but if you choose to use it then the setting can be found in Tools \ Options.

[pod]

I believe I have a bit of code that does what you are looking for, ... of course you will have to make some modifications

Option Compare Database
Option Explicit

Sub findAndDeleteFiles()
    Dim myPath
    myPath = "C:\databasefolder\"
    Dim fileSysObj, folderObj, singleFile, fileArray
    Set fileSysObj = CreateObject("Scripting.FileSystemObject")
    Set folderObj = fileSysObj.GetFolder(myPath )
    Set fileArray = folderObj.Files
    '------------
    'looping through the files
    For Each singleFile In fileArray
       'I suggested you put some more filters here
       ' such as filename extension = ".mdb"
       If singleFile.DateLastModified < DateAdd("Y", -30, Now) Then
            MsgBox singleFile.Name & singleFile.DateLastModified
            singleFile.Delete it
        End If
    Next

End Sub

[NeoPa]

Always be very careful copy/pasting code from anywhere on the web. Always review it if you can, before applying it to your own project (as pod has suggested in their post). Particularly important is to ensure that module-level code includes the line :

Option Explicit

If you ever work in VBA without this line in all of your modules then you're asking for trouble (as we seem to point out here every other day it seems). Hopefully it's rare to find such code posted on Bytes, as we catch and change it, but always be careful of that especially.

[Mariostg]

@Neopa. I clearly understand you Neopa and I know about the on close repair&compact ability. But in my case, which I guess is different, I don't want to do that on close, but before I run an automated update/manipulation routine. With 160 users, the db does not close ofen.

[twinnyfo]

NeoPa,

I tried playing with the Dir() functions, but have never used that before, and could only identify one file in the directory.

Thank you, Pod, for giving me a skeleton to work with. Here is what I did. I was able to loop through the files (I ONLY have backed up DB files in that folder, all with the same type of time stamp embedded in the file name, i.e., "DBFILENAME - 2012-06-23-07-35.accdb"). So, I find the timestamp, convert it to a date using CDate, then compare that date to the current date minus 30 days. If it meets those criteria, I delete the file.

Thank you both for setting me in the right direction. I hope the code below can help others out there in the future.

Option Compare Database 
Option Explicit 

Dim fileSysObj
Dim folderObj
Dim singleFile
Dim fileArray
Set fileSysObj = CreateObject("Scripting.FileSystemObject")
Set folderObj = fileSysObj.GetFolder(strBACKUPPATH)
Set fileArray = folderObj.Files
For Each singleFile In fileArray
    If Date - 30 > CDate(Mid(singleFile, Len(singleFile) - 21, 10)) Then
        singleFile.Delete
    End If
Next

[NeoPa]

Twinnyfo:
I tried playing with the Dir() functions, but have never used that before, and could only identify one file in the directory.

A call to Dir() with no parameters will repeat the last call for you and will successively return all matching files. It explains it in detail on the Help page. I prefer to use code from the basic libraries where I can, and always recommend the same, but there's no reason the other approach won't work. It simply means you either have to reference extra libraries, or like this code, use late-binding. I'm not a fan of late-binding either, as it makes for lazy code (IMHO) which is harder to maintain as you don't have the compiler on your side with enough information available to catch potential mistakes. I always prefer mistakes are caught at compile-time rather than at run-time.

As I say though, both approaches can produce workable code :-)

[twinnyfo]

Ahhhhhh, NeoPa! You make Grasshopper very smart!

Now that I understand how to use Dir(), I am much more comfortable with it. This also seems to streamline the code. Here is my latest attempt....I'm open to suggestions.

I've included my EH code, and if there is a Type Mismatch (i.e. the file name does not meet the proper time stamp requirements) I just go ahead and Kill the file anyway, because there shouldn't be anything else in this particular directory.

Very swift, my friend.... I learned something today!

Option Compare Database  
Option Explicit  

Private Sub DeleteOldFiles()
On Error GoTo EH:
    Dim strFileName As String
    strFileName = Dir(DirectoryName)
    Do While strFileName <> ""
        If Date - 30 > CDate(MID(strFileName, Len(strFileName) - 21, 10)) Then
            Kill DirectoryName & strFileName
        End If
        strFileName = Dir()
    Loop
    Exit Sub
EH:
    If Err.Number = 13 Then
        Resume Next
    Else
        MsgBox Err.Number & " " & Err.Description
        Exit Sub
    End If
End Sub

Thank you, again!

:-)

[NeoPa]

I'm very pleased that helped Twinnyfo, but I must explain that selecting the Best Answer is generally not something we allow for a post from the OP theirself. If there's one post that, more than any other, helped you get to your solution then please select that. Otherwise, we do appreciate your posting your eventual solution, we simply don't encourage such posts to be flagged as Best Answer (I'm sure you will see various reasons why if you give it just a little thought).

Congratulations on your code anyway mind you :-)

[twinnyfo]

No Problem! Have a great day! :-)

[Megalog]

I really dislike parsing text to extract dates from filenames, when you can use FSO to retrieve the actual dates saved or modified!

Here's what I use to retrieve that metadata:

Option Explicit

Public Function GetDateCreated(strFileName As String) As Date

    Dim oFSO As Object
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    GetDateCreated = oFSO.getfile(strFileName).DateCreated

End Function

So with that function available, you would change line #9 to:

If Date - 30 > GetDateCreated(strFileName) Then

Just my picky $.02

Edit: Forgot to mention that Pod's posting was close to this, but uses .DateLastModifi ed, which returns the date the original file was modified, not when it was backed up.

[twinnyfo]

Megalog,

Yes, I considered using the the date last modified, but as mentioned in the text above, not all the DB BEs are accessed all the time (thus the date last modified is not updated during the backup process), sometimes for several weeks. This could result in the backups possibly being deleted because their date last modified goes beyond 30 days.

The nature of my Q related to the file system procedures, which I, admittedly, have little experience in. NeoPa's guidance was useful in getting me on the right track to using the file system in the way I needed--as unorthodox as my file naming convention may be, but, within satisfactory limits for my needs.

I do appreciate your insights concerning this issue, though, because there certainly is much less CPU time taken in comparing a date to a date, rather than converting to a date and then comparing. However, since I am not handling huge numbers of files, I have seen no degradation in performance. I do use code similar to yours for some other functions of my DB, so your comments help confirm some of my practices... Which, again, I appreciate! :-)

Thank you for sharing your insights. Y'all help make me a better coder!