Access: Use VBA to copy files

=========
QUESTION
———
How do I copy, move, rename files from within the VBA code of my Access project?

———
How do I write a file backup routine into my Microsoft Access project/form?

 

=========
ANSWER
———
See EXAMPLES

 

=========
EXAMPLES
———    
    DoCmd.Hourglass True
    DoCmd.SetWarnings False
   
    'store a serial number in the parms table
    'then read that serial number to +1 and name your backup file.
    'then read that serial number to -5 to know which old backup file to delete.
    'store FileName and FilePath in the Parms table as well
   
    Dim rst As Recordset
   
    Set rst = CurrentDb.OpenRecordset("Parms")
    rst.MoveFirst
   
    Dim BackupSerialNew As Integer
    BackupSerialNew = rst!BackupSerial + 1
   
    Dim strSQL_BackupSerial As String
    strSQL_BackupSerial = "UPDATE

[Parms] SET BackupSerial = '" & BackupSerialNew & "'"
    DoCmd.RunSQL strSQL_BackupSerial
   
    Me.CurrentStatus = _
    "Creating backup of database file named: " & vbCrLf & _
    rst!FileName & "–COPYSerial_" & BackupSerialNew & "_" & Format(Date, "YYYY-MM-DD") & rst!FileExt & vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    "=========" & vbCrLf & _
    Me.CurrentStatus
    Me.Repaint
   
    Dim fs As Object
   
    Set fs = CreateObject("Scripting.FileSystemObject")
    fs.CopyFile rst!FilePath & "" & rst!FileName & rst!FileExt, rst!FilePath & "" & rst!FileName & "–CopySerial_" & BackupSerialNew & "_" & Format(Date, "YYYY-MM-DD") & rst!FileExt
    On Error Resume Next
    Kill rst!FilePath & "" & rst!FileName & "–CopySerial_" & BackupSerialNew - rst!BackupCount & "*"
   
    Set fs = Nothing
   
    Me.CurrentStatus = _
    "Backup file created: " & vbCrLf & _
    rst!FilePath & "" & rst!FileName & "–COPYSerial_" & BackupSerialNew & "_" & Format(Date, "YYYY-MM-DD") & rst!FileExt & vbCrLf & _
    vbCrLf & _
    "File: " & rst!FileName & "–COPYSerial_" & BackupSerialNew - rst!BackupCount & "_<DATE>.mdb and earlier deleted." & vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    Me.CurrentStatus
    Me.Repaint

    retval = Shell("explorer.exe " & rst!FilePath, vbNormalFocus)
    
    DoCmd.Hourglass False
    DoCmd.SetWarnings True

   

=========
APPLIES TO / KEY WORDS
———
Microsoft Access
File System
VBA

 

=========
REF
———
http://forums.devarticles.com/microsoft-access-development-49/copy-file-using-vba-30811.html

 


http://www.anysitesupport.com/access-use-vba-to-copy-files/
http://anySiteHosting.com

By | 2017-12-01T23:47:49+00:00 May 4th, 2011|Access, Computers, Documentation, Microsoft, VBA|0 Comments