Deleting oldest file

[Guest]

i have an Access2003 database that when closes creates a backup of the
backend mdb file. the backup files filename contains the date of the backup
in its file name. for examples MyDatabaseBackup 01 November 2006.bak

the folder where all these .bak files are stored has to potential to grow
out of control so im trying to limited the size of the backup folder to 10
backup files. ive created a function that counts the files. Im now tring to
create a function that when the count = 11 then delete the oldest file but i
dont know how to find the oldest file using code.

Can anybody assist please?

 

[Van T. Dinh]

Check Access VB Help on the FileDateTime() function.

 

[Guest]

StuJol,

Could you please post your VBA on how you run your back up? ---Or can
someone else please illustrate the VBA to do this, I am trying to do the same
thing...Thanks!

 

[Guest]

Here is a function that will do what you want. You only need to change the
names and path to suit your needs:

Public Sub DeleteOldestFile()
Dim dtmOldTime As Date
Dim strOldName As String
Dim lngCtr As Long

dtmOldTime = Now()
With Application.FileSearch
.NewSearch
.LookIn = "C:\documents and settings\Bozo\my documents"
.filename = "*.bak"
.Execute
If .FoundFiles.Count >= 11 Then
For lngCtr = 1 To .FoundFiles.Count
If FileDateTime(.FoundFiles(lngCtr)) < dtmOldTime Then
dtmOldTime = FileDateTime(.FoundFiles(lngCtr))
strOldName = .FoundFiles(lngCtr)
End If
Next lngCtr
If MsgBox("old file " & strOldName & vbNewLine & "old date " _
& dtmOldTime, vbQuestion + vbYesNo, "Delete This File") _
= vbYes Then
Kill strOldName
End If
End If
End With

End Sub

 

[Guest]

XP

my Access2003 database is split into a FE and BE. when i close my
mainform/switchboard on the database i have an unbound form open. This form
acts as a message box telling me how many days it were since i last did a
backup and gives me the option to create a backup or cancel.

to backup your BE file simple use the following code

First i compact the BE before baking it up. The compacted BE is renamed as a
temp file as a result of the compact. then i rename the temp file back to the
original file name.

Function Compact()

' Compact the Back-End database to a temp file.
DBEngine.CompactDatabase "C:\Program Files\Easara\Database\Easara
Database.mdb", "C:\Program Files\Easara\Database\Easara Database Temp.mdb"


' Delete the previous backup file if it exists.
If Dir("C:\Program Files\Easara\Database\Easara Database.bak") <> "" Then
Kill "C:\Program Files\Easara\Database\Easara Database.bak"
End If


' Rename the current database as backup and rename the temp file to the
original file name.
Name "C:\Program Files\Easara\Database\Easara Database.mdb" As
"C:\Program Files\Easara\Database\Easara Database.bak"
Name "C:\Program Files\Easara\Database\Easara Database Temp.mdb" As
"C:\Program Files\Easara\Database\Easara Database.mdb"


End Function

to create a backup of the compacted BE use the following code

Dim SourceFile, DestinationFile

SourceFile = "C:\Program Files\Easara\Database\Easara Database.bak" '
Define source file name.
DestinationFile = "C:\Program Files\Easara\Backups\Easara Database " &
Format(Date, "long date") & ".bak" ' Define target file name.

FileCopy SourceFile, DestinationFile

Hpoe this helps you

 

[Guest]

many thanks Klatuu. Worked great and now i also know that their is a
filesearch function available if i ever need it again.

Many Thanks

 

[Guest]

Glad to help. filesearch is not a function, it is an object.