Date diff file name

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am working on a program to backup my backend database. I have a couple
questions. First I want to only keep a daily backup for each day of the
week. So I need to kill the old one. I created a table with SaveDate and
WeekDay. I numbered the WeekDay like 1, 2, etc. So I should only have at the
most 7 records. Ok so I created a backup of the backend end and renamed it
like so Program_Dat.mdb to Program_Dat_3_18_2005.mdb. Now what I am trying
to do is check to see if there is a backend 7 days back if so kill it else
move on. How do I do a date diff this way? My second question is I want to
create a monthly backup as well, but only want to keep 6 months worth. I was
thinking of doing the same type of process, but there are 12 months rather
than 6. So does anyone have a concept on how to do this? I am sure one will
fix the other. Plus put them in the apporpriate folders. Thanks so much in
advance.
 
Ok I have done some more work. I can add new backups to a certain folder
with backend's name as described below. I can also delete the old one if it
was 7 days ago. Everything is working fine, however I need to create a
system to create monthly backups as well. I thought this would be easy since
I was able to figure out the daily backups per week, but it's not. I am
hoping someone can assist me on figuring this out. I only want to keep 6
months worth. I have a table called tblMonthlyBackup with SaveDate wich is a
date field and txtMonth which is a number feild, 1-6.

So I have 2 parts to think about.
First is checking the table to see if this month is present in the table if
not then add it. I believe it must look at each row. Also, if all 6 rows
are filled then it should find the one with the oldest date then edit that
one with today's date else add new with today's date.

next is the part where I check the file's name to find the one that is
Program_Dat_9-18-2005.mdb, that is the one which is 6 months old. So it
should actually check the month and year more so than the day. But how do
you check the files date? For the day one I did something like

OldDate = Format(Date - 7, "dd-mmm-yyyy")
strSaveNameOld = Left$(DataMdb, Len(DataMdb) - 4) & "_" & OldDate & ".mdb"
strSaveNameOldFile = BackupDirPath & BackupDailyAndName & strSaveNameOld

which shows the location and name of file that is seven days old. Any
suggestions On how to do the monthly one would be greatly appreciated.
 
Fysh,

Would it suit your purposes to simply include a day number or a month
number in the file name? For example, with the daily backups name the
file like this...
"Program_Dat" & Weekday(Date()) & ".mdb"
.... and with the monthly backups, like this...
"Program_Dat" & Month(Date() Mod 6 & ".mdb"

Then, whenever you do a backup on a Monday, last Monday's file will be
overwritten, etc, and when you do the July backup, the January file will
be overwritten, etc.
 
Steve thanks for the response. Actually I was thinking of the same thing
this morning when I woke up. The reason is if say for instance last Monday
is a holiday so when the procedure runs this Monday it won't find last
Monday. The one that was there 2 Mondays ago will still be there and won't
be over written. So to answer your question YES. I will either have to do
it that way or edit the table with the files name and then compare to see if
exist. My procedure on a hidden form when the program opens is like this. I
have other classes and modules that are called or checked during this
sequence. Watch for word wrap. I will post back if I run into problems.
Thanks

Private Sub Form_Load()
Dim dbs As Database
Dim rstLastBack As Recordset
Dim rs As Recordset
Dim strWhere As String
Dim strSQL As String
Dim tDate As Variant
Dim MDate As Variant
tDate = Weekday(Date)
MDate = Month(Date)
' Check to see if the FE and BE are linked
If CheckLinks() = False Then
If RelinkTables() = False Then
DoCmd.Close acForm, "Startup"
CloseCurrentDatabase
End If
End If
DoCmd.SetWarnings False
'Check to see if user chose the type of computer
If DMax("[Computer]", "qryRunSetup") = 0 Then
'If no computer type is chosen the start intial setup
DoCmd.OpenForm "frmInitialProgramSetup"
ElseIf DMax("[Computer]", "qryRunSetup") = 1 Then
'If Tablet PC has been chosen in past then open Tablet Main Screen
DoCmd.OpenForm "frmMainTablet"
ElseIf DMax("[Computer]", "qryRunSetup") = 2 Then
'If Main PC has been chosen in past check to see if backup has been
performed then open Main Screen
Set dbs = CurrentDb()
Set rstLastBack = dbs.OpenRecordset("tblLastBackup")
Call BackupDatabaseCheck(True)

If rstLastBack![LastBackupDate] < Date Then
strSQL = ("SELECT * " & " FROM tblBackUpInfo " & " WHERE
([txtWeekDay] = " & tDate & ")")
Set rs = dbs.OpenRecordset(strSQL)
strWhere = "txtWeekDay = " & rs![txtWeekDay]
rs.FindFirst strWhere
If Not rs.NoMatch Then
rs.Edit
rs![savedate] = Format(Date, "dd-mmm-yyyy")
rs.Update
rs.Close
Set rs = Nothing
End If
strSQL = ("SELECT * " & " FROM tblBackUpInfoMonthly " & "
WHERE ([txtMonth] = " & MDate & ")")
Set rs = dbs.OpenRecordset(strSQL)
strWhere = "txtMonth = " & rs![txtMonth]
rs.FindFirst strWhere
If Not rs.NoMatch Then
rs.Edit
rs![savedate] = Format(Date, "dd-mmm-yyyy")
rs.Update
rs.Close
Set rs = Nothing
End If
rstLastBack.Edit
rstLastBack!LastBackupDate = Date
rstLastBack.Update
rstLastBack.Close
Set rstLastBack = Nothing
Else
rstLastBack.Close
Set rstLastBack = Nothing
End If
Set dbs = Nothing
DoCmd.OpenForm "frmMain"
End If
DoCmd.SetWarnings True
End Sub
 
Ok I took your suggestion and it seems to work for the daily backup. Instead
of actually put the date in the thread it places the day of the week. On the
other hand I was able to place the monthly backup in the appropriate Monthly
backup folder, but the thread shows 12 instead of 3 for the Month of March.
Here is part of my class that I am using can you take a look to see where I
might be making a mistake? In th table it actually updates the appropriate
month with the date of lastbackup. I got the backup program from here, but
edit it for my particular use.
http://www.rogersaccesslibrary.com/OtherLibraries.asp
Called AutoBackupDemo

Thanks

Dim F_Result As Boolean

Dim intReturn As Integer
Dim strSaveNameOld As String
Dim strSaveNameOldFile As String
Dim strSaveMonthOld As String
Dim strSaveMonthOldFile As String
Dim strSaveDaily As String
Dim strSaveMonthly As String

F_Result = False

strSaveDaily = Left$(DataMdb, Len(DataMdb) - 4) & "_" & Weekday(Date) &
".mdb"
strSaveDaily = BackupDirPath & BackupDailyAndName & strSaveDaily
strSaveMonthly = Left$(DataMdb, Len(DataMdb) - 4) & "_" & Month(Date Mod
6) & ".mdb"
strSaveMonthly = BackupDirPath & BackupMonthlyAndName & strSaveMonthly
strSaveNameOld = Left$(DataMdb, Len(DataMdb) - 4) & "_" & Weekday(Date)
& ".mdb"
strSaveNameOldFile = BackupDirPath & BackupDailyAndName & strSaveNameOld
strSaveMonthOld = Left$(DataMdb, Len(DataMdb) - 4) & "_" & Month(Date
Mod 6) & ".mdb"
strSaveMonthOldFile = BackupDirPath & BackupMonthlyAndName &
strSaveNameOld

intReturn = vbYes

If intReturn = vbYes Then

With Application.FileSearch
.LookIn = BackupDirPath & BackupDailyAndName
.Filename = strSaveNameOld
If .Execute > 0 Then
Kill strSaveNameOldFile
DBEngine.CompactDatabase BEPathAndName, strSaveDaily
Else
DBEngine.CompactDatabase BEPathAndName, strSaveDaily
End If
End With

With Application.FileSearch
.NewSearch
.LookIn = BackupDirPath & BackupMonthlyAndName
.Filename = strSaveMonthOld
If .Execute > 0 Then
Kill strSaveMonthOldFile
DBEngine.CompactDatabase BEPathAndName, strSaveMonthly
Else
DBEngine.CompactDatabase BEPathAndName, strSaveMonthly
End If
End With
F_Result = True
End If

SaveDatabaseCopy = F_Result
 
Ok I had to redo some areas because if certain things happened, plus I had my
call function in the wrong place. I only want it to call the function once
per day. One thing to get my monthly program to work was I had to do the 12
month version. I still am unable to only keep six months worth. Here is my
code for the form and part of the class which is called. These are the areas
in which I have to do most of the editing. By chance could you show or
explain how to keep 6 months worth vs 12 months worth? If it can't be done
then we will have to settle for the latter, but there is one thing I learned
with Access is that almost anything is possible.

Private Sub Form_Load()
Dim dbs As Database
Dim rstLastBack As Recordset
Dim rs As Recordset
Dim strWhere As String
Dim strSQL As String
Dim tDate As Variant
Dim MDate As Variant

' Check to see if the FE and BE are linked
If CheckLinks() = False Then
If RelinkTables() = False Then
DoCmd.Close acForm, "frmOpen"
CloseCurrentDatabase
End If
End If
DoCmd.SetWarnings False
'Check to see if user chose the type of computer
If DMax("[Computer]", "qryRunSetup") = 0 Then
'If no computer type is chosen the start intial setup
DoCmd.OpenForm "frmInitialProgramSetup"
ElseIf DMax("[Computer]", "qryRunSetup") = 1 Then
'If Tablet PC has been chosen in past then open Tablet Main Screen
DoCmd.OpenForm "frmMainTablet"
ElseIf DMax("[Computer]", "qryRunSetup") = 2 Then
'If Main PC has been chosen in past check to see if backup has been
performed then open Main Screen
Set dbs = CurrentDb()
Set rstLastBack = dbs.OpenRecordset("tblLastBackup")
If rstLastBack![LastBackupDate] < Date Then
Call BackupDatabaseCheck(True)
strSQL = ("SELECT * " & " FROM tblBackUpInfo " & " WHERE
([txtWeekDay] = " & Weekday(Date) & ")")
Set rs = dbs.OpenRecordset(strSQL)
strWhere = "txtWeekDay = " & rs![txtWeekDay]
rs.FindFirst strWhere
If Not rs.NoMatch Then
rs.Edit
rs![SaveDate] = Date
rs.Update
rs.Close
Set rs = Nothing
End If
strSQL = ("SELECT * " & " FROM tblBackUpInfoMonthly " & "
WHERE ([txtMonth] = " & Month(Date) & ")")
Set rs = dbs.OpenRecordset(strSQL)
strWhere = "txtMonth = " & rs![txtMonth]
rs.FindFirst strWhere
If Not rs.NoMatch Then
If Year(rs![SaveDate]) = Year(Date) = True Then
Else
rs.Edit
rs![SaveDate] = Format(Date, "dd-mmm-yyyy")
rs.Update
rs.Close
Set rs = Nothing
End If
End If
rstLastBack.Edit
rstLastBack!LastBackupDate = Date
rstLastBack.Update
rstLastBack.Close
Set rstLastBack = Nothing
Else
rstLastBack.Close
Set rstLastBack = Nothing
End If
Set dbs = Nothing
DoCmd.OpenForm "frmMain"
End If
DoCmd.SetWarnings True
End Sub


Private Function SaveDatabaseCopy() As Boolean
On Error GoTo SaveDatabaseCopy

Dim F_Result As Boolean

Dim intReturn As Integer
Dim strSaveNameOld As String
Dim strSaveNameOldFile As String
Dim strSaveMonthOld As String
Dim strSaveMonthOldFile As String
Dim strSaveDaily As String
Dim strSaveMonthly As String

F_Result = False

strSaveDaily = Left$(DataMdb, Len(DataMdb) - 4) & "_" & Weekday(Date) &
".mdb"
strSaveDaily = BackupDirPath & BackupDailyAndName & strSaveDaily
strSaveMonthly = Left$(DataMdb, Len(DataMdb) - 4) & "_" & Month(Date) &
".mdb"
strSaveMonthly = BackupDirPath & BackupMonthlyAndName & strSaveMonthly
strSaveNameOld = Left$(DataMdb, Len(DataMdb) - 4) & "_" & Weekday(Date)
& ".mdb"
strSaveNameOldFile = BackupDirPath & BackupDailyAndName & strSaveNameOld
strSaveMonthOld = Left$(DataMdb, Len(DataMdb) - 4) & "_" & Month(Date) &
".mdb"
strSaveMonthOldFile = BackupDirPath & BackupMonthlyAndName &
strSaveMonthOld

intReturn = vbYes

If intReturn = vbYes Then
If DLookup("SaveDate", "tblBackUpInfo", "txtWeekDay =" &
Weekday(Date) & "And SaveDate= " & Date) Then
Else
With Application.FileSearch
.LookIn = BackupDirPath & BackupDailyAndName
.Filename = strSaveNameOld
If .Execute > 0 Then
Kill strSaveNameOldFile
DBEngine.CompactDatabase BEPathAndName, strSaveDaily
Else
DBEngine.CompactDatabase BEPathAndName, strSaveDaily
End If
End With
End If
If DLookup("SaveDate", "tblBackUpInfoMonthly", "txtMonth =" &
Month(Date) & "And Year([SaveDate])=" & Year(Date)) Then
Else
With Application.FileSearch
.NewSearch
.LookIn = BackupDirPath & BackupMonthlyAndName
.Filename = strSaveMonthOld
If .Execute > 0 Then
Kill strSaveMonthOldFile
DBEngine.CompactDatabase BEPathAndName, strSaveMonthly
Else
DBEngine.CompactDatabase BEPathAndName, strSaveMonthly
End If
End With
End If
F_Result = True
End If

SaveDatabaseCopy = F_Result
 
Ok sorry for the multiple entries, but I did some more work and figured out a
few things that need to be done. First I would have to check to see if the
folders exist if not then create them. Then I had to check to see if an
older file exist if so delete it then add new one. I also had to edit the
frmOpen to do a few more checks to see if anything needed to be edited.
Anyway here is some of my class code. I still haven't been able to keep just
6 months worth. If by chance you can take a look here to see how I can edit
it for that purpose I would appreciate it. I keep any eye on this string,
but for right now I need to develop some more reports. Thanks, P.S. watch
out for word wrap.

Private Function SaveDatabaseCopy() As Boolean
On Error GoTo SaveDatabaseCopy

Dim F_Result As Boolean

Dim intReturn As Integer
Dim strSaveNameOld As String
Dim strSaveNameOldFile As String
Dim strSaveMonthOld As String
Dim strSaveMonthOldFile As String
Dim strSaveDaily As String
Dim strSaveMonthly As String

F_Result = False

strSaveDaily = Left$(DataMdb, Len(DataMdb) - 4) & "_" & Weekday(Date) &
".mdb"
strSaveDaily = BackupDailyAndName & strSaveDaily
strSaveMonthly = Left$(DataMdb, Len(DataMdb) - 4) & "_" & Month(Date) &
".mdb"
strSaveMonthly = BackupMonthlyAndName & strSaveMonthly
strSaveNameOld = Left$(DataMdb, Len(DataMdb) - 4) & "_" & Weekday(Date)
& ".mdb"
strSaveNameOldFile = BackupDailyAndName & strSaveNameOld
strSaveMonthOld = Left$(DataMdb, Len(DataMdb) - 4) & "_" & Month(Date) &
".mdb"
strSaveMonthOldFile = BackupMonthlyAndName & strSaveMonthOld

intReturn = vbYes

If intReturn = vbYes Then
If DLookup("SaveDate", "tblBackUpInfo", "txtWeekDay =" &
Weekday(Date) & "And SaveDate= " & Date) Then
Else
' Check to see if the directory for Backups exisit
If Len(Dir(BackupDirPath, vbDirectory)) = False Then
'Create directory for Backups
MkDir (BackupDirPath)
End If
'Check to see if the directory for daily backup exist
If Len(Dir(BackupDailyAndName, vbDirectory)) = True Then
With Application.FileSearch
'Search to see if an older version of daily backup
exist
.LookIn = BackupDailyAndName
.Filename = strSaveNameOld
If .Execute > 0 Then
'If so delete old version and compact BE and
copy file to daily backup folder
Kill strSaveNameOldFile
DBEngine.CompactDatabase BEPathAndName,
strSaveDaily
Else
'If not then compact BE and copy file to daily
backup folder
DBEngine.CompactDatabase BEPathAndName,
strSaveDaily
End If
End With
Else
'Create directory for daily backup
MkDir (BackupDailyAndName)
'Compact BE and copy file to daily backup folder
DBEngine.CompactDatabase BEPathAndName, strSaveDaily
End If
End If
If DLookup("SaveDate", "tblBackUpInfoMonthly", "txtMonth =" &
Month(Date) & "And Year([SaveDate])=" & Year(Date)) Then
Else
'Check to see if the directory for monthly backup exisit
If Len(Dir(BackupMonthlyAndName, vbDirectory)) = True Then
With Application.FileSearch
'Search to see if an older version of monthly backup exist
.NewSearch
.LookIn = BackupMonthlyAndName
.Filename = strSaveMonthOld
If .Execute > 0 Then
'If so delete old version and compact BE and copy
file to monthly backup folder
Kill strSaveMonthOldFile
DBEngine.CompactDatabase BEPathAndName, strSaveMonthly
Else
'If not then compact BE and copy file to monthly
backup folder
DBEngine.CompactDatabase BEPathAndName, strSaveMonthly
End If
End With
Else
'Create directory for daily backup
MkDir (BackupMonthlyAndName)
'Compact BE and copy file to monthly backup folder
DBEngine.CompactDatabase BEPathAndName, strSaveMonthly
End If
End If
F_Result = True
End If

SaveDatabaseCopy = F_Result

SaveDatabaseCopyExit:
Exit Function

SaveDatabaseCopy:
Resume SaveDatabaseCopyExit
End Function
 
. Ok so I created a backup of the backend end and renamed it
like so Program_Dat.mdb to Program_Dat_3_18_2005.mdb. Now what I am
trying to do is check to see if there is a backend 7 days back
Click to expand...

You have noticed that m-d-y dates cannot easily be compared. On the other
hand, if you use a sensible date format the whole problem becomes
trivial:

Private function BackUpFileName(SomeDate As Date) As String
BackUpFileName = _
"Program_Dat_" & Format(SomeDate,"yyyy_mm_dd")

End Function




' later in the active code...
' check if the other file is older than a week ago
If SomeOtherFileName < BackUpFileName(Date()-7) Then

' it's old, so destroy it
Kill SomeOtherFileName

End If




Hope that helps


Tim F
 
Thanks, I will take a look at that.

Tim Ferguson said:
You have noticed that m-d-y dates cannot easily be compared. On the other
hand, if you use a sensible date format the whole problem becomes
trivial:

Private function BackUpFileName(SomeDate As Date) As String
BackUpFileName = _
"Program_Dat_" & Format(SomeDate,"yyyy_mm_dd")

End Function




' later in the active code...
' check if the other file is older than a week ago
If SomeOtherFileName < BackUpFileName(Date()-7) Then

' it's old, so destroy it
Kill SomeOtherFileName

End If




Hope that helps


Tim F
Click to expand...
 
Back
Top