[RESOLVED] DB Backup and Restore

[Simply Me]

I have come accross of this Thread post #5. It was posted 2004 but it is very helpful and I am going to add this code to my app. Cudos to RhinoBull.

I have some question though. I have been reading alot from the thread that before backing up the Database is should be closed. Im sorry, I have to ask this question. Is it not I have to put a button on a form and on click the button it will back-up the DB. If so then, the DB is open because the program is running. How can I back-up then?

Also I want to check whether a file already exist or not and want to auto back-up the DB say before the beginning of each month (should detectwhether at the time of the scheduled back-up there is no work (ex: Holiday, Saturday or Sunday) so the back-up will not take place).

Any solution for this?

[RobDog888]

That is because it was assumed that you were connecting to the db using ADO or some other data access object connectivity.

It sounds like your wanting to do this from withing Access itself (VBA)?

[Simply Me]

That is because it was assumed that you were connecting to the db using ADO or some other data access object connectivity.

It sounds like your wanting to do this from withing Access itself (VBA)?

I am using ADODB. In my program i have a menu Backup Database (using Treeview). When Clicked a form will be shown. I put the code of RhinoBull in a button, it is working fine. In doing this, the DB is open right?

[RobDog888]

Correct, your ADODB connection object(s) and recordsets need to be closed in order to prevent any database corruption and possibility of loosing any recordset data.

[Simply Me]

so how do i do back-up then if i risk losing any data or worse DB corruption? I want to back up my database either automatically or when the user find it so to back it up. Here's the code from RhinoBull and i tried it and its working.

Option Explicit
 
Private Sub Form_Load()
    'check for archive folder and create one if it doesn't exist
    If Dir(App.Path & "\Archive", vbDirectory) = "" Then
        MkDir App.Path & "\Archive"
    End If
    'check for error folder and create one if it doesn't exist
    If Dir(App.Path & "\Error", vbDirectory) = "" Then
        MkDir App.Path & "\Error"
    End If
End Sub
 
Private Sub Command1_Click()
    ArchiveDB App.Path & "\MasterDB.mdb", _
              App.Path & "\Archive\" & Format(Now, "mm_dd_yyyy") & ".mdb"
End Sub
 
Public Function ArchiveDB(sSource As String, sDestination As String) As Boolean
'===============================================================================
On Error GoTo ErrHandler
    
    FileCopy sSource, sDestination
    Exit Function
 
ErrHandler:
'------------
    Open App.Path & "\Error\erros.txt" For Append As #1
        Print #1, Now() & vbTab & Err.Number & ": " & Err.Description
    Close #1
    Exit Function
 
End Function

[RobDog888]

Before you call ArchiveDB, close your connection(s) like so.

oCnn.[color=black]Close[/color]
ArchiveDB App.Path & "\MasterDB.mdb", App.Path & "\Archive\" & Format(Now, "mm_dd_yyyy") & ".mdb"
oCnn.Open

[Simply Me]

thanks!
how about this concern?

Also I want to check whether a file already exist or not and want to auto back-up the DB say before the beginning of each month (should detectwhether at the time of the scheduled back-up there is no work (ex: Holiday, Saturday or Sunday) so the back-up will not take place).

[RobDog888]

You can use the Dir() function to test for the file existance or a more complex but reliable way like my code here - http://www.vbforums.com/showpost.php...10&postcount=3

[vivek_master146]

If u want to check whether a file exists or not then this is the code:

Dim afile As String
Private Sub Command1_Click()
afile = Dir$("c:\My Documents\smashing.exe") <> ""
If afile Then
MsgBox "File exist"
Else
MsgBox "File does not exist"
 
End If
 
End Sub

[Simply Me]

You can use the Dir() function to test for the file existance or a more complex but reliable way like my code here - http://www.vbforums.com/showpost.php...10&postcount=3

Great code RobDog888. I'll just do some modification in such a way that it would give the user an option to use save as function.

the autoback-up can it be done?

[Simply Me]

I added the code below to let the user save the DB to a new file.

'Check if File Exists or Not
  If FileExists(App.Path & "\Archive\" & Format(Now, "mm_dd_yyyy") & ".mdb") = 1 Then
    If (MsgBox("File Exists! Save it using New Filename?", vbInformation + vbYesNo) = vbYes) Then
      With CommonDialog1
        'Set Filters
        .Filter = "Access Database Files (*.mdb)|*.mdb|"
        ' Display the Open dialog box
        .ShowSave
          'App.Path & "\Archive\" & Format(Now, "mm_dd_yyyy") & ".mdb", _
          'App.Path & "\Archive\" & Format(Now, "mm_dd_yyyy") & ".mdb", 0
      End With
    Else
      Exit Sub
    End If
  Else
    'oConn.Close 'Needed to protect the DB from possible corruption
    ArchiveDB App.Path & "\MasterDB.mdb", _
      App.Path & "\Archive\" & Format(Now, "mm_dd_yyyy") & ".mdb"
    'oConn.Open
    'MsgBox "File Not Found!"
  End If

This works, however, I want that when the user will save the DB using another Name the commondialog will open the path \Archive\....

[Simply Me]

When I try to change the filename and click save button there was no error but there was no file saved at all. How come?

[Simply Me]

I just added this line after .showsave and it worked except that when i clicked cancel without typing any filename i get an error path/file access error.I also need to know is how to put default filename. The default filename should increment like in Microsoft word Document1, document2 and so on.

Open cdbDialog.FileName For Output As #1
Print #1,
Close #1

Any suggestions or code snippet?

[rory]

If you are interested in compacting the DB also.. here is an example ...
i use this in my ASP stuff mostly but also works in VB. Basically it takes
The source file and makes a compacted backup.

Public Function CompactDatabase(source, destination)

    Dim objJetEngine
    Set objJetEngine = CreateObject("JRO.JetEngine")

    objJetEngine.CompactDatabase _
        "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _
        & source & ";Jet OLEDB:Database Password=mypass;", _
        "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _
        & destination & ";Jet OLEDB:Database Password=mypass;"

    Set objJetEngine = Nothing

End Function

[rory]

Updated ..
Also Turn on Common Dialog's Cancel Error in Properties.

Private Sub Command1_Click()

Const ProgName = "Export Database"
Dim sFile As String
Dim sFileName As String

On Error GoTo err_cancel:
    sFile = Format(Now, "mm_dd_yyyy") & ".mdb"
    If Dir$(App.Path & "\Archive\" & sFile) <> vbNullString Then
        If (MsgBox("File Exists! Save it using New Filename?", vbInformation + vbYesNo, ProgName) = vbYes) Then
            CommonDialog1.InitDir = App.Path & "\Archive\"
            CommonDialog1.Filter = "Mdb Files (*.mdb)|*.mdb|All Files (*.*)|*.*"
            CommonDialog1.FileName = sFile
            CommonDialog1.DialogTitle = "Export Database"
            CommonDialog1.ShowSave
            sFileName = CommonDialog1.FileName
            FileCopy App.Path & "\MasterDB.mdb", sFileName
        Else
            Exit Sub
        End If
    Else
        If Dir$(App.Path & "\Archive\", vbDirectory) = vbNullString Then
            Call MkDir(App.Path & "\Archive")
        End If
        sFileName = App.Path & "\Archive\" & sFile
        FileCopy App.Path & "\MasterDB.mdb", sFileName
    End If
    If Dir$(sFileName) <> vbNullString Then
        MsgBox "Export Completed", vbInformation, ProgName
    Else
       MsgBox "Export Error!", vbInformation, ProgName
    End If
        Exit Sub
err_cancel:
        Exit Sub  
End Sub

[Simply Me]

Great code! thanks rory. Maybe instead of autoback. I would like that VB detect whether its the first day of the month and prompt the user to back up the database(assuming there is work on that day). If however, the first day of the month falls on a non-working day, the program should be able to detect the following day or so that the DB is not yet back up. Can this be Done?

[rory]

Here is part of it . .. Updated

Dim Firstofmonth As Date
    Dim Lastofmonth As Date
    Dim ThisDay As String
    Dim YesterDay As String
    
    Firstofmonth = DateAdd("d", 1, Date - Day(Date))
    Lastofmonth = DateAdd("m", 1, Date - Day(Date))
    ThisDay = Format(Now, "dddd")
    YesterDay = Format(Now - 1, "dddd")
    
    Debug.Print Firstofmonth
    Debug.Print Lastofmonth
    Debug.Print ThisDay
    Debug.Print YesterDay
    
    If Date = Firstofmonth Then
        Debug.Print "Today is first of Month"
    End If
    If ThisDay <> "Saturday" And ThisDay <> "Sunday" Then
        Debug.Print "Today is a Weekday"
    End If
    If YesterDay = "Sunday" Then
        Debug.Print "Today is a First Of Week"
    End If

[Simply Me]

i just noticed. When do i call the code u posted in #15?

[rory]

Thats a button, but you could call it on exit ..?

Private Sub Form_Unload(Cancel As Integer)
  
  Dim sFile As String
  Dim Answer As Integer
  sFile = Format(Now, "mm_dd_yyyy") & ".mdb"
  If Dir$(App.Path & "\Archive\" & sFile) = vbNullString Then
        Answer = MsgBox("Would you like to backup now", _
        vbQuestion + vbYesNo, "My Program")
        If Answer = vbYes Then
            Call BackUpDatabase
            'Cancel = -1
        End If
   End If
 
End Sub
 
Private Sub BackUpDatabase()
 
Const ProgName = "Export Database"
Dim sFile As String
Dim sFileName As String
 
On Error GoTo err_cancel:
    sFile = Format(Now, "mm_dd_yyyy") & ".mdb"
    If Dir$(App.Path & "\Archive\" & sFile) <> vbNullString Then
        If (MsgBox("File Exists! Save it using New Filename?", vbInformation + vbYesNo, ProgName) = vbYes) Then
            CommonDialog1.InitDir = App.Path & "\Archive\"
            CommonDialog1.Filter = "Mdb Files (*.mdb)|*.mdb|All Files (*.*)|*.*"
            CommonDialog1.FileName = sFile
            CommonDialog1.DialogTitle = "Export Database"
            CommonDialog1.ShowSave
            sFileName = CommonDialog1.FileName
            FileCopy App.Path & "\MasterDB.mdb", sFileName
        Else
            Exit Sub
        End If
    Else
        If Dir$(App.Path & "\Archive\", vbDirectory) = vbNullString Then
            Call MkDir(App.Path & "\Archive")
        End If
        sFileName = App.Path & "\Archive\" & sFile
        FileCopy App.Path & "\MasterDB.mdb", sFileName
    End If
    If Dir$(sFileName) <> vbNullString Then
        MsgBox "Export Completed", vbInformation, ProgName
    Else
       MsgBox "Export Error!", vbInformation, ProgName
    End If
    '// End
        Exit Sub
err_cancel:
        Exit Sub
End Sub

[Simply Me]

Im sorry rory its post #14 not #15. My mistake. I dont need to use the code in Post #15 on exit of a form. I already added did some modification to it to suite my needs. Anyway thanks for the additional info you posted.

Going back to post #14 when do i call the Function CompactDatabase?

[rory]

Instead of using FileCopy you would use this.
As it makes a compacted copy of the original.

Ofcourse you want to make sure the Database Objects are closed first.

CompactDatabase App.Path & "\MasterDB.mdb", sFileName

Or this would also delete the main file and copy the compacted file as the main file, after compacting.

CompactDatabase App.Path & "\MasterDB.mdb", sFileName
Kill App.Path & "\MasterDB.mdb"
FileCopy sFileName, App.Path & "\MasterDB.mdb"

[Simply Me]

Ofcourse you want to make sure the Database Objects are closed first.

I tried putting this line to close the connection to DB but it wont work.

Oconn.Close

Not DB back up at all. I have a module which i call to connect to my DB.
I have also an MDIMain which I put a Treeview as my menu. On load of my MDIMain i have not called yet connection. Also when i click the Backup menu item the form will show where i put those codes. In this form i have not called yet the connection. So is there a need to close the DB Connection?

Note: Later I will be putting log in form. In case, I will be opening the DB to check for the Correct login name and password.

[rory]

Right, if its not open there is nothing to close.

Though you could always put this in your close connection sub to only close it if it is open.

If Not Oconn Is Nothing Then
    If Oconn.State = adStateOpen Then Oconn.Close
End If
Set Oconn = Nothing

[Simply Me]

Right, if its not open there is nothing to close.

Though you could always put this in your close connection sub to only close it if it is open.

If Not Oconn Is Nothing Then
    If Oconn.State = adStateOpen Then Oconn.Close
End If
Set Oconn = Nothing

this is what i have

If oConn.State = adStateOpen Then
    oConn.Close
    Set oConn = Nothing
  End If

I think they're the same?

[RobDog888]

No, they are not the same as the first one is checking if the object is initialized and then its state where the second is only checking the state. If the object is not initialized then you will get a 91 error using the second.

[Simply Me]

so the two should be put in one sub? like

Public Sub closeConnection()
 
 If Not oConn Is Nothing Then
   If oConn.State = adStateOpen Then oConn.Close
End If
 
Set oConn = Nothing
 
  If oConn.State = adStateOpen Then
    oConn.Close
    Set oConn = Nothing
  End If
end Sub

[RobDog888]

No, just this one.

Public Sub closeConnection()
    If Not oConn Is Nothing Then
        If oConn.State = adStateOpen Then oConn.Close
    End If
End Sub

[Simply Me]

ok done!

I tried the code in Post #17. i just changed the highlighted part with this

If Date = ThisDay Then
        'call the form where the backup button is located
    End If

I put it on MDIMain Load but its not working. I tried it too on Activate event. Nothing happened

If Date = [HL="#80FF80"]Firstofmonth [/HL]Then
        Debug.Print "Today is first of Month"
    End If

[RobDog888]

Place a breeakpoint on MDIMain_Load and step through the code and tell me what is actually happening. Is Firstofmonth a valid date? If so whats the value?

[Simply Me]

I tried the breakpoint and step through it. it is not executing the higlighted part. it goes at once to end if

If Date = ThisDay Then
       [HL="#FFFF80"]MsgBox "test"[/HL]
    End If

[rory]

I tried the breakpoint and step through it. it is not executing the higlighted part. it goes at once to end if

If Date = ThisDay Then
       [HL="#FFFF80"]MsgBox "test"[/HL]
    End If

ThisDay and Yesterday are Strings - Days of the Week.

What are you trying to compare?

[Simply Me]

I tested it again and i found out that the firstday of the week in the program is Monday. It should be Sunday. I change this part If YesterDay = "Sunday" Then into If YesterDay = "Saturday" Then and nothing happened. Anyone?

UPDATE: that part of code above is now ok.

[Simply Me]

My mistake. Im testing it with todays date so it will not work. Sorry!

[RobDog888]

the Date function returns a date type value consisting of something like this, 05/14/2006, depending on your system settings.

It will never equal "Sunday".

You will want to use the WeekDay function instead of the actual Date.

If Weekday(Date) = vbSunday Then 'vbSunday = 1. vbMonday = 2, etc.

[Simply Me]

thanks again Robdog888.
here's what's happening now. When i put the the code on MDIForm Load it displays first the msgbox and then when ok button is click it display the frmbackup form.

How do i let the mdi form load first and then msgbox and then the frmbackup?

Private Sub MDIForm_Load()
  StatusBar1.Panels(2).Text = "Time: " & Format(Time, "hh:mm:ss AM/PM")
  StatusBar1.Panels(3).Text = "Date: " & Date
  
 'Display the Treeview
  Call makeTreeView
  Call PromptBackup
End Sub
 
 
Private Sub PromptBackup()
 
    Dim Firstofmonth As Date
    Dim Lastofmonth As Date
    Dim ThisDay As String
    Dim YesterDay As String
    
    Firstofmonth = DateAdd("d", 1, Date - Day(Date))
    Lastofmonth = DateAdd("m", 1, Date - Day(Date))
    ThisDay = Format(Now, "dddd")
    YesterDay = Format(Now - 1, "dddd")
    
    If Date = Firstofmonth Then
        MsgBox "You need to Back up your Database"
        frmBackup.Show vbModal
        'Debug.Print "Today is first of Month"
    End If
    If ThisDay <> "Saturday" And ThisDay <> "Sunday" Then
        Debug.Print "Today is a Weekday"
    End If
    If YesterDay = "Saturday" Then
        Debug.Print "Today is a First Of Week"
    End If
End Sub

[RobDog888]

You can place the "Call PromptBackup" in the Activate event but then you will need to create a boolean flag variable to only let it call it once or when the user minimizes or switches apps, when they come back it will active again.

Or you could use a timer that is set to enabled in the last line of the MDIForm_Load event and an interval of about 500 (30 seconds) to fire its Timer event procedure where you wil have the call to your "Call PromptBackup" call. Disable the timer as the first call as the next line of code should be the one calling the backup call..

[Simply Me]

You can place the "Call PromptBackup" in the Activate event but then you will need to create a boolean flag variable to only let it call it once or when the user minimizes or switches apps, when they come back it will active again.

Or you could use a timer that is set to enabled in the last line of the MDIForm_Load event and an interval of about 500 (30 seconds) to fire its Timer event procedure where you wil have the call to your "Call PromptBackup" call. Disable the timer as the first call as the next line of code should be the one calling the backup call..

Got it working!

One last Problem. What if the first day of the month falls on Saturday or Sunday (no work, computers are off) is it still possible that vb would check if DB was backed-up or not?

[rory]

Got it working!

One last Problem. What if the first day of the month falls on Saturday or Sunday (no work, computers are off) is it still possible that vb would check if DB was backed-up or not?

Dim Firstofmonth As Date
    Dim Lastofmonth As Date
    Dim DateBefore As Date
    Dim DateBeforeThat As Date
    Dim ThisDay As String
    Dim YesterDay As String
    
    Firstofmonth = DateAdd("d", 1, Date - Day(Date))
    Lastofmonth = DateAdd("m", 1, Date - Day(Date))
    ThisDay = Format(Now, "dddd")
    YesterDay = Format(Now - 1, "dddd")
    DateBefore = DateAdd("d", -1, Date)
    DateBeforeThat = DateAdd("d", -2, Date)
    
    Debug.Print Firstofmonth
    Debug.Print Lastofmonth
    Debug.Print DateBefore
    Debug.Print DateBeforeThat
    Debug.Print ThisDay
    Debug.Print YesterDay
    
    If Date = Firstofmonth Then
        Debug.Print "Today is first of Month"
    End If
    
    If ThisDay <> "Saturday" And ThisDay <> "Sunday" Then
        Debug.Print "Today is a Weekday"
    End If
    
    If YesterDay = "Sunday" Then
        Debug.Print "Today is a First Of Week"
    End If
    
    If DateBefore = Firstofmonth And ThisDay = "Monday" Then
        Debug.Print "Day Before Was First of Month"
    ElseIf DateBeforeThat = Firstofmonth And ThisDay = "Monday" Then
        Debug.Print "Day Before That Was First of Month"
    End If

[rory]

so your code would look like this ..

Private Sub PromptBackup()
 
    Dim Firstofmonth As Date
    Dim DateBefore As Date
    Dim DateBeforeThat As Date
    Dim ThisDay As String
    
    Firstofmonth = DateAdd("d", 1, Date - Day(Date))
    DateBefore = DateAdd("d", -1, Date)
    DateBeforeThat = DateAdd("d", -2, Date)
    ThisDay = Format(Now, "dddd")
    
    If Date = Firstofmonth Or _
       DateBefore = Firstofmonth And ThisDay = "Monday" Or _
       DateBeforeThat = Firstofmonth And ThisDay = "Monday" Then
            MsgBox "You need to Back up your Database"
            frmBackup.Show vbModal
    End If
 
End Sub

[Simply Me]

rory where would i put the code in post #38, in a module?