By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,121 Members | 1,706 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,121 IT Pros & Developers. It's quick & easy.

Find old files and delete them

twinnyfo
Expert Mod 2.5K+
P: 3,284
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:

Expand|Select|Wrap|Line Numbers
  1. 'Database Path constants in another module
  2. Public Const strDBASEPATH As String = "\\ServerName\DATABASE\"
  3. Public Const strBACKUPPATH As String = "\\ServerName\DATABASE\BACKUP\"
  4.  
  5. 'Code within my start up form
  6. If Me.txtLastBackup < Now - 0.25 Then
  7.     Dim FSO
  8.     Dim strDate As String
  9.     strDate = Format(Date, "yyyy-mm-dd") & "-" & Format(Time, "hh-mm")
  10.     Set FSO = CreateObject("Scripting.FileSystemObject")
  11.     FSO.CopyFile strDBASEPATH & "DATA\DATA-BE1.accdb", strBACKUPPATH & "DATA-BE1 BACKUP - " & strDate & ".accdb"
  12.     FSO.CopyFile strDBASEPATH & "DATA\DATA-BE2.accdb", strBACKUPPATH & "DATA-BE2 BACKUP - " & strDate & ".accdb"
  13.     FSO.CopyFile strDBASEPATH & "DATA\DATA-BE3.accdb", strBACKUPPATH & "DATA-BE3 BACKUP - " & strDate & ".accdb"
  14.     FSO.CopyFile strDBASEPATH & "DATA\DATA-BE4.accdb", strBACKUPPATH & "DATA-BE4 BACKUP - " & strDate & ".accdb"
  15.     Me.txtLastBackup = Now
  16. End If
  17.  
Jul 23 '12 #1

✓ answered by 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.

Share this Question
Share on Google+
13 Replies


NeoPa
Expert Mod 15k+
P: 31,494
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.
Jul 23 '12 #2

100+
P: 332
If it might help a bit on your space issue, you could use the method DBEngine.CompactDatabase as part of your backup routine.
Jul 23 '12 #3

NeoPa
Expert Mod 15k+
P: 31,494
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.
Jul 23 '12 #4

pod
100+
P: 298
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


Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Sub findAndDeleteFiles()
  5.     Dim myPath
  6.     myPath = "C:\databasefolder\"
  7.     Dim fileSysObj, folderObj, singleFile, fileArray
  8.     Set fileSysObj = CreateObject("Scripting.FileSystemObject")
  9.     Set folderObj = fileSysObj.GetFolder(myPath )
  10.     Set fileArray = folderObj.Files
  11.     '------------
  12.     'looping through the files
  13.     For Each singleFile In fileArray
  14.        'I suggested you put some more filters here
  15.        ' such as filename extension = ".mdb"
  16.        If singleFile.DateLastModified < DateAdd("Y", -30, Now) Then
  17.             MsgBox singleFile.Name & singleFile.DateLastModified
  18.             singleFile.Delete it
  19.         End If
  20.     Next
  21.  
  22. End Sub
Jul 23 '12 #5

NeoPa
Expert Mod 15k+
P: 31,494
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 :
Expand|Select|Wrap|Line Numbers
  1. 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.
Jul 23 '12 #6

100+
P: 332
@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.
Jul 23 '12 #7

twinnyfo
Expert Mod 2.5K+
P: 3,284
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.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database 
  2. Option Explicit 
  3.  
  4. Dim fileSysObj
  5. Dim folderObj
  6. Dim singleFile
  7. Dim fileArray
  8. Set fileSysObj = CreateObject("Scripting.FileSystemObject")
  9. Set folderObj = fileSysObj.GetFolder(strBACKUPPATH)
  10. Set fileArray = folderObj.Files
  11. For Each singleFile In fileArray
  12.     If Date - 30 > CDate(Mid(singleFile, Len(singleFile) - 21, 10)) Then
  13.         singleFile.Delete
  14.     End If
  15. Next
  16.  
Jul 23 '12 #8

NeoPa
Expert Mod 15k+
P: 31,494
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 :-)
Jul 24 '12 #9

twinnyfo
Expert Mod 2.5K+
P: 3,284
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!

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database  
  2. Option Explicit  
  3.  
  4. Private Sub DeleteOldFiles()
  5. On Error GoTo EH:
  6.     Dim strFileName As String
  7.     strFileName = Dir(DirectoryName)
  8.     Do While strFileName <> ""
  9.         If Date - 30 > CDate(MID(strFileName, Len(strFileName) - 21, 10)) Then
  10.             Kill DirectoryName & strFileName
  11.         End If
  12.         strFileName = Dir()
  13.     Loop
  14.     Exit Sub
  15. EH:
  16.     If Err.Number = 13 Then
  17.         Resume Next
  18.     Else
  19.         MsgBox Err.Number & " " & Err.Description
  20.         Exit Sub
  21.     End If
  22. End Sub
  23.  
Thank you, again!

:-)
Jul 24 '12 #10

NeoPa
Expert Mod 15k+
P: 31,494
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 :-)
Jul 24 '12 #11

twinnyfo
Expert Mod 2.5K+
P: 3,284
No Problem! Have a great day! :-)
Jul 24 '12 #12

Megalog
Expert 100+
P: 378
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:

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Public Function GetDateCreated(strFileName As String) As Date
  4.  
  5.     Dim oFSO As Object
  6.     Set oFSO = CreateObject("Scripting.FileSystemObject")
  7.     GetDateCreated = oFSO.getfile(strFileName).DateCreated
  8.  
  9. End Function
So with that function available, you would change line #9 to:

Expand|Select|Wrap|Line Numbers
  1. If Date - 30 > GetDateCreated(strFileName) Then 
Just my picky $.02

Edit: Forgot to mention that Pod's posting was close to this, but uses .DateLastModified, which returns the date the original file was modified, not when it was backed up.
Jul 26 '12 #13

twinnyfo
Expert Mod 2.5K+
P: 3,284
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!
Jul 26 '12 #14

Post your reply

Sign in to post your reply or Sign up for a free account.