Solved Delete previous versions of a backup (1 Viewer)

zelarra821

Registered User.
Local time
Today, 14:41
Joined
Jan 14, 2019
Messages
803
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

I’m here to help
Staff member
Local time
Today, 06:41
Joined
Oct 29, 2018
Messages
21,357
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

Immoderate Moderator
Staff member
Local time
Today, 08:41
Joined
Feb 28, 2001
Messages
26,999
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.


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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:41
Joined
Oct 29, 2018
Messages
21,357
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.
Code:
?cdate(format(val("2020 10 21 Tienda BD"),"0000-00-00"))
 

zelarra821

Registered User.
Local time
Today, 14:41
Joined
Jan 14, 2019
Messages
803
Thanks! I tried this in the Immediate Window and got a date out of it. Maybe you could start with that.
Code:
?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

I’m here to help
Staff member
Local time
Today, 06:41
Joined
Oct 29, 2018
Messages
21,357
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

Registered User.
Local time
Today, 14:41
Joined
Jan 14, 2019
Messages
803
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.


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!

Code:
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

ScreenShot002.jpg
 

zelarra821

Registered User.
Local time
Today, 14:41
Joined
Jan 14, 2019
Messages
803
Code:
            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

I’m here to help
Staff member
Local time
Today, 06:41
Joined
Oct 29, 2018
Messages
21,357
Code:
            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

Registered User.
Local time
Today, 14:41
Joined
Jan 14, 2019
Messages
803
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

I’m here to help
Staff member
Local time
Today, 06:41
Joined
Oct 29, 2018
Messages
21,357
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.
 

Users who are viewing this thread

Top Bottom