Automatic backups of tables using ADO 2

[PROXI]

I am not that knowledgeable in many ways. I don't really understand your code to capture the corruption. I am much better at reading code and understanding it then coding it ;(

Thanks,

PROXI

 

[PROXI]

Ok... I am not quite sure how to modify this to do the backup if the time in the file is more than 1 hour old. This is what I have tried to use:

If ThisString > CVDate(ThisString) - 1 Then

Its not working it just does a backup everytime I open the database. Help me out man... Please.

Thanks,

PROXI

 

[N2Life]

PROXI,

Have to visit a customer now. Will try to get back to you later, but can't be sure that will be possible today.

N2Life

 

[N2Life]

PROXI,

You could probably make good use of the DateDiff function for this; something on the order of:

TimePassed = DateDiff(TimeInterval, xThen, xNow)

Use TimeInterval = "n" for minutes.

N2Life

 

[PROXI]

... ok... at the chance of sounding like a retard.... how and where would I put this into the module? I'm Guessing Here:

[b]If Date <> CVDate(ThisString) Then[/b]
    MsgBox "It is time to backup all the tables." & vbCrLf & vbCrLf & _
        "When you click on OK, this will happen.  You will receive a message once " & _
        "this activity is complete.", vbInformation
    For Each tbl In cat.Tables
        If tbl.Type = "LINK" Then
            mySQL = "SELECT * INTO " & tbl.Name & " IN '" & _
                WhereFile & "' from " & tbl.Name
            DoCmd.RunSQL mySQL
        End If
    Next

If that is true... how would i put that in?

Thanks,

PROXI

 

[PROXI]

'If date on file is not today's date, then no backup has been made today.
If DateDiff("n", ThisString, Now()) > 60 Then _
    MsgBox "It is time to backup all the tables." & vbCrLf & vbCrLf & _
        "When you click on OK, this will happen.  You will receive a message once " & _
        "this activity is complete.", vbInformation
    For Each tbl In cat.Tables
        If tbl.Type = "LINK" Then
            mySQL = "SELECT * INTO " & tbl.Name & " IN '" & _
                WhereFile & "' from " & tbl.Name
            DoCmd.RunSQL mySQL
        End If
        DoCmd.SetWarnings False
    Next

' Write today's date to the text file. If the form that called this routine is opened again later today,
'   nothing will happen.
    Open ThisFile For Output As #1
    Write #1, Now
    Close #1
    MsgBox "Today's backups have been made.", vbInformation

End Sub

This is what I have and it just backs it up each time that I open the database... how do I get it to work man?

Thanks,

PROXI

 

[N2Life]

PROXI,

There are 3 solutions. Take your pick.

1. You can go the OnTimer route. Check other threads in Tek-Tips for help on that if you need it. As I said before, I stay away from OnTimer.

2. You can program a button on a convenient form with the code you described in your latest posting. Any time the button is clicked, backups will be made if the time difference is more than 60 minutes.

3. You can put the code behind the OnLoad event for a form that is frequently opened in your database. Or some similar approach.

Best of luck,

N2Life

 

[PROXI]

That is the thing. I have this code run every time that my switchboard is opened. That is the first thing that comes up in my database. But for some reason it decides to do it each time that it opens whether it was over or under and hour ago. Also.. it is giving warning about going to overwrite tables. How do I stop it from doing this. I thought that the docmd.setwarnings = false took care of that?

Thanks,

PROXI

 

[PROXI]

N2Life:
I finally got it to do an hourly backup on this. Here is the full module for anyone that wishes to do the same in the future:

Option Compare Database
Function dbBackup()
Call MakeBackupsADO(FileNamePattern:="Delivery_Backup_")
End Function

Public Sub CreateBackupTextFile()
' This sub presupposes the existence of a folder named BACKUPS, located in the directory in
' which this database is stored.  It creates a text file there; the file will be used to
' determine whether daily backups of tables in this database have been made.
' This sub should be run only ONCE.

Dim DataPath As String
Dim ThisString As Variant
Dim ThisFile As String
Dim WhereFile As String
Dim TheDay As String
Dim FileNamePattern As String

' -----  Insert correct file name pattern here  -------------------------------------------------
FileNamePattern = "Delivery_Backup_"

DataPath = "\\Eausrv01\Eau01a11\shared" & "\BACKUPS\"

TheDay = Day(Date)
If Len(TheDay) = 1 Then
    TheDay = "0" & TheDay
End If

WhereFile = DataPath & FileNamePattern & TheDay & ".mdb"
ThisFile = DataPath & "\KeepThis_" & FileNamePattern & "File.txt"

' Write today's date to the file.
Open ThisFile For Output As #1
Write #1, Now
Close #1
MsgBox "The special text file required for backups has been created." & _
vbCrLf & vbCrLf & "It is named: " & ThisFile, vbInformation

End Sub


Public Sub CreateBackupDatabasesADO()
' This sub presupposes the existence of a folder named BACKUPS, located in the directory in
' which this database is stored.
' It creates 31 databases to be used to store daily backups of tables from this database.
' This sub should be run only ONCE.

Dim Ktr As Integer
Dim WhereFile As String
Dim TheDay As String
Dim DataPath As String
Dim FileNamePattern As String
Dim NameOfDB As String
Dim newDB As ADOX.Catalog

' -----  Insert correct file name pattern here  -------------------------------------------------
FileNamePattern = "Delivery_Backup_"

DataPath = "\\Eausrv01\Eau01a11\Shared" & "\BACKUPS\"
WhereFile = DataPath & FileNamePattern
Set newDB = New ADOX.Catalog

For Ktr = 1 To 31
    'Adjust the day to 2 characters
    TheDay = Trim(CStr(Ktr))
    If Len(TheDay) = 1 Then
        TheDay = "0" & TheDay
    End If

    'Define the location and name of the new database
    NameOfDB = WhereFile & TheDay & ".mdb"

    'Create the database
    newDB.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & NameOfDB
Next Ktr

Set newDB = Nothing

MsgBox "All databases have been prepared as " & WhereFile & "nn."
End Sub


Public Sub MakeBackupsADO(FileNamePattern As String)
' This subroutine presupposes the existence of 31 Access databases stored in a folder named BACKUPS
' in the same subdirectory where this database is stored.
' It also presupposes the existence of a text file (Example: KeepThis_MyDatabaseName_Backup_File.txt)
' stored in the BACKUPS folder.
' Every day, when this routine is first called, there is an automatic transfer of the data from all
' the tables into the backup database for that day of the month (example, MyDatabaseName_Backup_15.mdb).
' This routine should be called, for example, when the main form is loaded. An example:
'           Private Sub Form_Load()
'           DoCmd.Maximize
'           Call MakeBackupsADO(FileNamePattern:="MyDatabaseName_Backup_")
'           End Sub

Dim DataPath As String
Dim ThisString As Variant
Dim ThisFile As String
Dim WhereFile As String
Dim TheDay As String
Dim mySQL As String
Dim cat As New ADOX.Catalog
Dim tbl As Table
Set cat.ActiveConnection = CurrentProject.Connection
DataPath = "\\Eausrv01\Eau01a11\Shared" & "\BACKUPS\"

' Capture day of month of today's date
TheDay = Day(Date)
' Make it 2 characters
If Len(TheDay) = 1 Then
    TheDay = "0" & TheDay
End If

' Get the date currently stored in the special text file
WhereFile = DataPath & FileNamePattern & TheDay & ".mdb"
ThisFile = DataPath & "\KeepThis_" & FileNamePattern & "File.txt"
Open ThisFile For Input As #1
Input #1, ThisString
Close #1

'If date on file is not today's date, then no backup has been made today.
If (DateDiff("n", ThisString, Now) < 60) = False Then _

    DoCmd.SetWarnings False
    MsgBox "It is time to backup all the tables." & vbCrLf & vbCrLf & _
        "When you click on OK, this will happen.  You will receive a message once " & _
        "this activity is complete.", vbInformation
    For Each tbl In cat.Tables
        If tbl.Type = "LINK" Then
            mySQL = "SELECT * INTO " & tbl.Name & " IN '" & _
                WhereFile & "' from " & tbl.Name
            DoCmd.RunSQL mySQL
        End If
        
    Next

' Write today's date to the text file. If the form that called this routine is opened again later today,
'   nothing will happen.
    Open ThisFile For Output As #1
    Write #1, Now
    Close #1
    MsgBox "Backup made at " & Now(), vbInformation
Else: MsgBox "No backup needed as of " & Now()
End If
End Sub

Thanks for all your help N2Life. I wish that I could give you more stars... but only one per person

Thanks,

PROXI