Automating db backup to another location on close

[Neil Bingham]

I work on several databases on my laptop. I would like to have macro/code so that when I close a db it is automatically copied to another location (ie my networked desktop pc or an external hard drive) I do this manually now - any code has to answer yes in the pop up box which says "file already exists - do you wish to overwrite" Can anyone help please?

[CJ_London]

your would need code in a form close event - and that form would need to be one that a) is open b) closes last and c) does not close at any other time.

there is no event that I am aware of which runs when the access application closes - only as above when the application closes forms - and there are ways which this might be circumnavigated (e.g. windows is closed)

That said, in your form close event you would have something like

Dim fso As Object

Set fso = VBA.CreateObject("Scripting.FileSystemObject")
If MsgBox("Do you want to backup this file?", vbYesNo) = vbYes Then
    On Error Resume Next
    fso.copyfile CurrentDb.Name, "C:" & Mid(CurrentDb.Name, InStrRev(CurrentDb.Name, "\")), False
    If Err.Number = 58 Then 'file already exists
        If MsgBox("File already exists - do you want to overwrite?", vbYesNo) = vbYes Then
            fso.copyfile CurrentDb.Name, "C:" & Mid(CurrentDb.Name, InStrRev(CurrentDb.Name, "\")), True
        End If
    End If
End If

change C: to the directory you want to use