Automated Database Backup On Close Event

I found a question from 2004 that referred to setting up an Access database to automatically create a backup On Close (http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_20949474.html).  There is a response in there by corhaveman that lists the code below.  I created a module and pasted it in.  I then created a form that opens as "hidden" when the DB opens.  I set up the On Close even with

Dim bkpath As String
bkpath = "C:\Data\TTdata.accdb"
BackupMade (bkpath)

"bkpath" is the correct path and filename of the backend of this database that holds all the data tables.  I can verify that both the path and file are correct and do exist.  For the moment, I would be very happy with a backup copy being saved in the same folder.  But, if anyone can quickly show me how to change the destination location of the backup file, that would be very kind as well!

When I close the database I was getting thrown in to the different errors.  So, I ticked out the error handling.  After doing that and trying to exit the database I get a runtime error 424: Object Required.  The problem is that the DB closes immediately after that so I cannot see what line of code is causing the issue.

Can anyone help me pan out the problem here and get this to work for me?  Thanks!

Public Function BackupMade(BackupDir as string)

Dim strBoriginal, strBKnew, ApplName As String
Dim Db As Database
Dim Td As Table
Dim n As Integer

On Error GoTo ErrHandle

    DoCmd.Hourglass False
    BackupMade = False
    Set Db = CurrentDb
    'get connectionstring
    strBoriginal = StrConnection
    n = Len(strBoriginal)
    Do Until Mid(strBoriginal, n, 1) = "\"
        n = n - 1
    Loop
    ApplName = Mid(strBoriginal, n + 1)
        
    strBKnew = BackupDir
    If DirExists(BackupDir) Then
        strBKnew = strBKnew & " " & Left(ApplName, Len(ApplName) - 4) & " " & Format(Now(), "yy-mm-dd hh-mm") & ".mdb"
        DBEngine.CompactDatabase strBoriginal, strBKnew
        Disconnect_Tables
        Kill strBoriginal
        FileCopy strBKnew, strBoriginal
        Connect_Tables (strBoriginal)
        MsgBox strBKnew & " is gemaakt" & Chr$(13) & _
            "DELETE OLD BACKUP-FILES!!", , "Backup-procedure correct"
        BackupMade = True
    Else
        MsgBox "Backup-file procedure has failed. Backup-directory not correct!", vbOKOnly
        BackupMade = False
    End If
    
ErrHandle_Exit:
    DoCmd.Hourglass False
    Exit Function

ErrHandle:
    MsgBox " Backup-file procedure has failed. Check backup-directory!", , "Backup-procedure failed"
    BackupMade = False
    Resume ErrHandle_Exit:

End Function

Public Function StrConnection() As String
'returns StrConnection incl. path, if no connection then ""

Dim Td As TableDef
Dim teller As Integer
Dim DataFile As String

    teller = 1
    StrConnection = ""
    For Each Td In CurrentDb.TableDefs 'first 5 internal
        If teller > 5 Then
            If Td.Connect <> "" Then
                StrConnection = Right(Td.Connect, Len(Td.Connect) - 10)
                Exit Function
            End If
        End If
        teller = teller + 1
    Next
    
End Function


Public Function DirExists(PathName) As Boolean
'PathName is directory
    
On Error GoTo F_Afh
    
    If GetAttr(PathName) = vbDirectory Then
        DirExists = True
    Else
        DirExists = False
    End If
    Exit Function
    
F_Afh:
    DirExists = False
    
End Function


Public Sub Disconnect_Tables()

Dim Td As TableDef
Dim teller As Integer

    teller = 1
    For Each Td In CurrentDb.TableDefs 'first 5 internal
        If teller > 5 Then
            If Td.Connect <> "" Then
                DoCmd.DeleteObject acTable, Td.Name
            End If
        End If
        teller = teller + 1
    Next
    
End Sub


Public Sub Connect_Tables(MyFile As String)

Dim Td As TableDef
Dim teller As Integer
Dim Db As Database

    Disconnect_Tables
    Set Db = OpenDatabase(MyFile)
    teller = 1
    For Each Td In Db.TableDefs 'eerste 5 intern
        If teller > 5 Then
            DoCmd.TransferDatabase acLink, "Microsoft Access", MyFile, , Td.Name, Td.Name
        End If
        teller = teller + 1
    Next
    Db.Close
End Sub

Open in new window

JohnMc0620Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
The  easiest way to specify semi-static path locations is to store them in a table.  If the table will be different for each user, I include the UserID so each person has custom settings.  Then I give the user a form he can use to save a different path.

Your error handling is getting in the way rather than helping.  You should trap for specific errors and give messages regarding that error.  All non-specifically trapped errors should display the Access error number and message.

Select Case err.Number
    Case 111    'error that will stop process
        MsgBox "message relating to err 111",vbokonly
        Exit Sub
    Case 222    'error you want to report but continue
        MsgBox "msg relating to err 222", vbokonly
        Resume Next
    Case 333    'error you want to ignore
         Resume Next
    Case Else    'all undefined errors
        MsgBox err.Number & "--" & err.Description
        Exit Sub
End Select
0
JohnMc0620Author Commented:
Pat,

Sorry, I'm a little confused.  Yes, I do plan on setting up this function to give an end user the ability to specify the destination path for the backup file.  That is why I am specifying the destination path as a variable (bkpath).  That way, once I have the function working, I can easily set up the front side of it to accept whatever path the end user specified and had stored in the table.  However, I think this comes later.  Right now, I am just trying to identify the problem of why this function isn't working and what I need to do to fix it.

That being said, I copied the error handling you gave me in to the function and now have it as below.  I also added a couple MsgBox(BackupDir) prompts inside the If statement in case the code didn't get to your errors.  And, sure enough, when I run it I get the following displays:

C:\Data\TTdata.accdb  <enter>
Backup-file procedure has failed. Backup-directory not correct! <enter>

Then the database closes.

That tells me that the If statement is coming back with a false parameter of whether it thinks the directory exists and is going right to the "Else".  Am I understanding that correctly?  The problem I have is that I have no idea why it would think that?

Public Function BackupMade(BackupDir As String)

Dim strBoriginal, strBKnew, ApplName As String
Dim Db As Database
Dim Td As Table
Dim n As Integer

'On Error GoTo ErrHandle

    DoCmd.Hourglass False
    BackupMade = False
    Set Db = CurrentDb
    'get connectionstring
    strBoriginal = StrConnection
    n = Len(strBoriginal)
    Do Until Mid(strBoriginal, n, 1) = "\"
        n = n - 1
    Loop
    ApplName = Mid(strBoriginal, n + 1)
        
    strBKnew = BackupDir
    If DirExists(BackupDir) Then
        strBKnew = strBKnew & " " & Left(ApplName, Len(ApplName) - 4) & " " & Format(Now(), "yy-mm-dd hh-mm") & ".mdb"
        DBEngine.CompactDatabase strBoriginal, strBKnew
        Disconnect_Tables
        Kill strBoriginal
        FileCopy strBKnew, strBoriginal
        Connect_Tables (strBoriginal)
        MsgBox strBKnew & " is gemaakt" & Chr$(13) & _
            "DELETE OLD BACKUP-FILES!!", , "Backup-procedure correct"
        BackupMade = True
    Else
        MsgBox (BackupDir)
        MsgBox "Backup-file procedure has failed. Backup-directory not correct!", vbOKOnly
        BackupMade = False
    End If
    
ErrHandle_Exit:
    DoCmd.Hourglass False
    Exit Function

ErrHandle:
    
    Select Case Err.Number
    Case 111    'error that will stop process
        MsgBox "message relating to err 111", vbOKOnly
        Exit Function
    Case 222    'error you want to report but continue
        MsgBox "msg relating to err 222", vbOKOnly
        Resume Next
    Case 333    'error you want to ignore
         Resume Next
    Case Else    'all undefined errors
        MsgBox Err.Number & "--" & Err.Description
        Exit Function
    End Select
    
    'MsgBox (BackupDir)
    'MsgBox " Backup-file procedure has failed. Check backup-directory!", , "Backup-procedure failed"
    'BackupMade = False
    Resume ErrHandle_Exit:

End Function

Open in new window

0
PatHartmanCommented:
But, if anyone can quickly show me how to change the destination location of the backup file, that would be very kind as well!
I guess  I though you'd know when I used 111, 222, 333 that you would know this was an example.  The point of the case statement is that some errors can be ignored, and others must be handled and sometimes you know in advance what the potential errors are and sometimes you don't.  When you don't know the actual error code, you don't want to interfere with the display of that.  You NEED to know the actual error code.

You commented out the GoTo error handler so the actual error code and message are not being displayed.  You might want to stick with the error messages you are displaying, although I doubt it since you'll never be able to debug this until you learn how to display the actual error.  It's like running into a room and shouting problem! problem! problem!  When you should be shouting fire! gun! water! tornado! or whatever
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
I think the problem is that you are passing the full path when it is expecting just the  directory for the backup.

Dim bkpath As String
bkpath = "C:\Data\TTdata.accdb"
BackupMade (bkpath)

Open in new window


should be

Dim bkpath As String
bkpath = "C:\Data\backups"  '  a folder name
BackupMade (bkpath)

Open in new window



Check out the parameter name:
Public Function BackupMade(BackupDir as string)

From a quick scan of the code you posted , it appears that you will need to be sure the backup directory (folder)  exists before calling the backup routine.

The code also looks at the first five tables to see if one is linked. If there is one, it uses that path to the back end for the backup source. You only pass where you want to back up to be placed.

I see lots of issues with the is code for what you want to do. I would not recommend it.


I prefer to use this method:  http://www.peterssoftware.com/aps.htm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JohnMc0620Author Commented:
Well, thanks for pointing out my ignorance.  I'll be sure to write out some notes on what to do the next time I go to the movies and a fire breaks out.  I sure don't want to forget what I should yell at everyone.  I know now that "problem" and "help" are obviously the wrong words to use.

While you've been spending your time telling me why I can't figure out the problem and that I won't ever figure it out because I am dumb, I actually figured out the problem.  

From the information I gave you earlier, I was able to determine that the "Public Function DirExists(PathName)" function was the root of the issue.  It was assigning the False statement to the If statement in the BackupMade function, pushing the result to it's Else section.

I was passing the full path, including file name to this function, when all it wanted was the path and no file name.  Once I corrected that, the entire function works.

I never said I was a knowledgeable and experienced programmer.  I just needed some bread crumbs to help me along.  Next time I'll yell it...

BREAD CRUMBS!!... BREAD CRUMBS!!!
0
JohnMc0620Author Commented:
I caught it while I was waiting for a response.  But, this is exactly correct and will solve the issue.  Thank you HiTechCoach for focusing your efforts on helping me solve the issue!  Very much appreciated!
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
Glad we could assist.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.