473,326 Members | 2,061 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Find old files and delete them

twinnyfo
3,653 Expert Mod 2GB
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.

13 6664
NeoPa
32,556 Expert Mod 16PB
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
Mariostg
332 100+
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
32,556 Expert Mod 16PB
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
298 100+
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
32,556 Expert Mod 16PB
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
Mariostg
332 100+
@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
3,653 Expert Mod 2GB
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
32,556 Expert Mod 16PB
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
3,653 Expert Mod 2GB
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
32,556 Expert Mod 16PB
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
3,653 Expert Mod 2GB
No Problem! Have a great day! :-)
Jul 24 '12 #12
Megalog
378 Expert 256MB
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
3,653 Expert Mod 2GB
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

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

Similar topics

1
by: war_wheelan | last post by:
I have a database file approximately 30GB in size which creates 3 data tables daily. I need to reduce the size of the DB due to disk size limitations. Could I EXPORT some tables, let us say over...
3
by: Qwerty | last post by:
How would I go about doing this? I want to be able to go through a target directory, find files with a certain format (.toc), and then I want to write new data to a line inside of the file. I...
2
by: graphicsxp | last post by:
Hi, How can I open all the files in a directory, which names match a particular string ? Say I have a string like 'a file name to find' and I want to find and open all the files of a given...
2
by: Birderman | last post by:
Hi, Using MS ACCESS 2003, i have a form for entering data. wih one object on the form (yes/no field) I want to inititiate vba code to run if it changes. I have no problem gettting it to update...
5
by: nayeef | last post by:
hello there i want to know a program which will find find a given variable in a list and replace/delete them according to our wish. Well it would be preferable if its in C. thanks
4
by: Theadmin77 | last post by:
I m kind of stuck ...i need to find files with a specific criteria but i can not make it work .... These are the ones : 1.How do you Find files under /root that contain the whole words (i.e.,...
7
by: AccessHunter | last post by:
I am using the following code to find and delete records in a table. The logic will go through each record and if a duplicate row is found will delete it. I ran this code and it worked the first...
4
by: =?Utf-8?B?QWxleA==?= | last post by:
Ther are tons of files in the WINDOWS foler of my Winsow XP. They all look similar. For Example: KB308304 KB009800 KB476855 .... or something like: Q123344 Q574485 Q955848
2
by: manishabh77 | last post by:
can anyone suggest how I can find files in directories and subdirectories and copy them into a new directory.I tried the following code but it doesnot do the job. $inputfile1 = "file_index.txt"; ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.