Solved Delete previous versions of a backup

[zelarra821]

Hi.

I have a code that I got from the Internet that copies the database to me in another location with the date included in the name, to make a backup copy.

I've been looking for a way to delete the "old versions". I have seen that it can be erased with VBA's Kill method. Now, I don't know if it is possible to delete X copies to leave only the last two, for example.

I don't know if I have managed to explain myself.

Thank you.

A greeting.

 

[theDBguy]

Should be possible, but it would take some code. First, we'll have to figure out the logic.

What does the filename look like?

 

[The_Doc_Man]

Because the backup copy has a date included in the name as text, a wildcarded operation might take too many files. You cannot easily delete by date without first decoding the name to extract that date. But if you use the File System Object, you can look at the folder to get a collection of files in that folder. Then you can look at the file's properties to get the creation date.

XL-central

www.xl-central.com

This code sample was used from Excel, not Access, but it demonstrates use of the file system object to look at files and their properties. If your backup code is predictable in when it runs, then you should have a way to set a date criterion to decide whether or not to delete the file. I.e. if the backup is more than x days old, kill it. Something like that might work for you.

OR you could create a table of file names and ages and use a TOP 2 query to identify the files you wanted to keep. Once you have the list of files, there are a gazillion ways to go to the next step.

 

[zelarra821]

Should be possible, but it would take some code. First, we'll have to figure out the logic.

What does the filename look like?

The name is, eg, 2020 10 21 Tienda BD.

 

[theDBguy]

The name is, eg, 2020 10 21 Tienda BD.

Thanks! I tried this in the Immediate Window and got a date out of it. Maybe you could start with that.

?cdate(format(val("2020 10 21 Tienda BD"),"0000-00-00"))

 

[zelarra821]

Thanks! I tried this in the Immediate Window and got a date out of it. Maybe you could start with that.

?cdate(format(val("2020 10 21 Tienda BD"),"0000-00-00"))

Thank you very much, but I have managed to add the backup copies that are created in a table.

 

[theDBguy]

Thank you very much, but I have managed to add the backup copies that are created in a table.

That's a good first step. You would still need some way to chronologically sort them, and the expression I gave you might help with that too.

 

[zelarra821]

Because the backup copy has a date included in the name as text, a wildcarded operation might take too many files. You cannot easily delete by date without first decoding the name to extract that date. But if you use the File System Object, you can look at the folder to get a collection of files in that folder. Then you can look at the file's properties to get the creation date.

XL-central

www.xl-central.com

This code sample was used from Excel, not Access, but it demonstrates use of the file system object to look at files and their properties. If your backup code is predictable in when it runs, then you should have a way to set a date criterion to decide whether or not to delete the file. I.e. if the backup is more than x days old, kill it. Something like that might work for you.

OR you could create a table of file names and ages and use a TOP 2 query to identify the files you wanted to keep. Once you have the list of files, there are a gazillion ways to go to the next step.

I have created a table, and through the code that I create the backup by, I add the date, time and name of the backup created.

How can I leave the last two in that table and delete the rest?

Thank you so much for the idea!

Private Sub Form_Close()
    CurrentDb.Execute "UPDATE T00Configuracion SET [RutaAplicacion]='" & Application.CurrentProject.Path & "'"
    If DLookup("[RutaAplicacion]", "[T00Configuracion]") = Application.CurrentProject.Path Then
        Dim fso As Object
        If DLookup("CopiaDeSeguridad", "T00Configuracion") = -1 Then
            If Dir(DLookup("RutaCopiaDeSeguridadDonde", "T00Configuracion"), vbDirectory) = "" Then
                Set Carpeta = CreateObject("Scripting.FileSystemObject")
                Carpeta.CreateFolder (DLookup("RutaCopiaDeSeguridadDonde", "T00Configuracion"))
            End If
            Set fso = CreateObject("Scripting.FileSystemObject")
            Dim NameDB As String
            NameDB = Year(Date) & " " & Month(Date) & " " & Day(Date) & " " & Format(Time, "hh.mm") & " " & Application.CurrentProject.Name
            fso.CopyFile Application.CurrentProject.Path & "\" & Application.CurrentProject.Name, DLookup("RutaCopiaDeSeguridadDonde", "T00Configuracion") & "\" & NameDB
            
            Dim rstBackup As DAO.Recordset
            
               Set rstBackup = CurrentDb.OpenRecordset("T00CopiasDeSeguridad")
            
               rstBackup.AddNew
               rstBackup!Fecha = Now
               rstBackup!Nombre = NameDB
               rstBackup.Update
                    
            If DLookup("CopiarImagenes", "T00Configuracion") = -1 Then
                If IsNull(DLookup("RutaCarpetaDonde", "T00Configuracion")) Then
                    RutaInicial = Application.CurrentProject.Path & "\Archivos"
                    Set Carpeta = CreateObject("Scripting.FileSystemObject")
                    Carpeta.CreateFolder (DLookup("RutaCopiaDeSeguridadDonde", "T00Configuracion") & "\Archivos")
                    RutaFinal = DLookup("RutaCopiaDeSeguridadDonde", "T00Configuracion") & "\Archivos"
                Else
                    RutaInicial = DLookup("RutaCarpetaDonde", "T00Configuracion")
                    RutaFinal = DLookup("RutaCopiaDeSeguridadDonde", "T00Configuracion") & "\Archivos"
                    If Dir(DLookup("RutaCopiaDeSeguridadDonde", "T00Configuracion") & "\Archivos", vbDirectory) = "" Then
                        Set Carpeta = CreateObject("Scripting.FileSystemObject")
                        Carpeta.CreateFolder (DLookup("RutaCopiaDeSeguridadDonde", "T00Configuracion") & "\Archivos")
                    End If
                End If
                Set fsoFolder = fso.GetFolder(RutaInicial)
                For Each fsoFile In fsoFolder.Files
                    strName = fsoFile.Name
                    fso.CopyFile RutaInicial & "\" & strName, RutaFinal & "\" & strName
                Next
            End If
        End If
    End If
End Sub

 

[Isaac]

You could simply load the filenames into an array, sort the array, then delete all but the last 2.

Recently discussed some useful techniques regarding this: https://www.access-programmers.co.uk/forums/threads/import-files-sequentially.314075/post-1722973

 

[zelarra821]

            Select Case DCount("Nombre", "T00CopiasDeSeguridad")
                Case 2
                    Kill DLookup("RutaCopiaDeSeguridadDonde", "T00Configuracion") & "\" & DMin("Nombre", "T00CopiasDeSeguridad", "Fecha=#" & DMin("Fecha", "T00CopiasDeSeguridad") & "#")
                    CurrentDb.Execute "DELETE * From T00CopiasDeSeguridad Where Nombre='" & DMin("Nombre", "T00CopiasDeSeguridad", "Fecha=#" & DMin("Fecha", "T00CopiasDeSeguridad") & "#") & "'"
            End Select

Solved.

If I put this before copying the file, there will always be two backup copies of the database: the last, and the newest; and the immediately preceding one.

 

[theDBguy]

            Select Case DCount("Nombre", "T00CopiasDeSeguridad")
                Case 2
                    Kill DLookup("RutaCopiaDeSeguridadDonde", "T00Configuracion") & "\" & DMin("Nombre", "T00CopiasDeSeguridad", "Fecha=#" & DMin("Fecha", "T00CopiasDeSeguridad") & "#")
                    CurrentDb.Execute "DELETE * From T00CopiasDeSeguridad Where Nombre='" & DMin("Nombre", "T00CopiasDeSeguridad", "Fecha=#" & DMin("Fecha", "T00CopiasDeSeguridad") & "#") & "'"
            End Select

Solved.

If I put this before copying the file, there will always be two backup copies of the database: the last, and the newest; and the immediately preceding one.

Hi. Glad to hear you got it sorted out. Of course, that limits you to only have two versions of your backup, which could be a problem if the last two backups were not good enough, and you needed to go farther back.

 

[zelarra821]

Yeah, sure, obviously it can be a problem if both of them are corrupt. My brother was about to lose a month of records in the POS that he has made in Access due to an error that he gave, and that has been the reason for doing this.

What I don't know is if, changing that 2 to 5, for example, would leave 5 backups. I say this to put a field where to choose the number of backup copies in the configuration form of the database that I developed, that I use for all the databases that I make, and in which you can choose the name of the database data, the favicon, if you want backups, the colors of the database ... If someone wants it, I can share it here. Do not expect wonders, that I handle myself at the user level in VBA.

Thank you all!

 

[theDBguy]

Yeah, sure, obviously it can be a problem if both of them are corrupt. My brother was about to lose a month of records in the POS that he has made in Access due to an error that he gave, and that has been the reason for doing this.

What I don't know is if, changing that 2 to 5, for example, would leave 5 backups. I say this to put a field where to choose the number of backup copies in the configuration form of the database that I developed, that I use for all the databases that I make, and in which you can choose the name of the database data, the favicon, if you want backups, the colors of the database ... If someone wants it, I can share it here. Do not expect wonders, that I handle myself at the user level in VBA.

Thank you all!

Hi. At least, you're way ahead of most by having an automated backup routine. Something is better than nothing, so I say you're in good shape. Good luck with your project.