none
VBA FileCopy

    Question

  • I inherited an Access 2003 database where a forms on load event copies a source file on the server and saves it as a backup copy in another location adding the date to the end of the destination file creating a new daily backup each day. I do not want all of these date specific backups being created. I want to change the event to copy and replace the existing destination file so as to have only one backup that is updated as needed. I removed the current date specific text from the statement that defines the target file name but the problem is that the event does not successfully copy and replace the file. I think that it is erroring out because it gets hung up on the message warning the user that the file already exists and do you want to replace it.  I tried a docmd setwarnings but it did not work. How can I fix this issue or am I way off base? Any help would be greatly appreciated. Thanks

    The vba without the date specific text and the set warnings is as follows (I have highlighted what I feel is the main issue):

    Private Sub Form_Load()
        Dim recTimeStamp As Recordset
        Dim dbCurrent As Database
        Dim SourceFile, DestinationFile, CompactFile As String
        Dim strDesc As String
       
        DBEngine.SystemDB = "\\server01\Production\PendCode\DailyPendCode.mdb"
       
        Set dbCurrent = CurrentDb()
        Set recTimeStamp = dbCurrent.OpenRecordset("Backup Time")
        strDesc = ""
       
        On Error GoTo Errhandler
       
          SourceFile = "\\server01\Production\PendCode\DailyPendCode.mdb"  ' Define source file name.
        DestinationFile = "C:\DatabaseBackups\DailyPendCode.mdb"   ' Define target file name.
       
        FileCopy SourceFile, DestinationFile    ' Copy source to target.
        
        strDesc = strDesc & "Copy complete. "
        strDesc = strDesc & "Backup completed successfully."
       
        GoTo Finish
       
    Errhandler:
        strDesc = strDesc & "Backup did not complete.  Error " & Err.Number & ": " & Err.Description
       
       
    Finish:
        'Add message and time to history table
        recTimeStamp.AddNew
        recTimeStamp("Date") = Now()
        recTimeStamp("Description") = strDesc
        recTimeStamp.Update
        recTimeStamp.Close
       
        Quit
    End Sub

    Tuesday, April 19, 2011 7:22 PM

Answers

  • Use the Kill() command to delete the previous backup before copying the new one.

    Kill "filepath"

    You may wish to copy to a dummy backup (leaving the previous) so you can be sure the filecopy doesn't error out, then kill the previous backup and use the Name() function (check Help on that) to rename the new backup to the final filename.

    hth

    • Marked as answer by Bruce Song Tuesday, April 26, 2011 5:37 AM
    Tuesday, April 19, 2011 11:39 PM

All replies

  • Use the Kill() command to delete the previous backup before copying the new one.

    Kill "filepath"

    You may wish to copy to a dummy backup (leaving the previous) so you can be sure the filecopy doesn't error out, then kill the previous backup and use the Name() function (check Help on that) to rename the new backup to the final filename.

    hth

    • Marked as answer by Bruce Song Tuesday, April 26, 2011 5:37 AM
    Tuesday, April 19, 2011 11:39 PM
  • Jack - Thank you. I appreciate the help.
    Wednesday, April 20, 2011 5:01 PM