[AccessD] Moving and recording files using vba in Access 2007

[Jeff Barrows]

I have a situation where I need to be able to click a button in Access and
move all of the files (all photos) from one folder to another.  Along with
moving them, which I can do, I need to be able to rename them if there is a
file with the same name.  I also need to be able to store path and file
name in an SQL table.

Has anyone done this before?

I know that this is pretty rough, but here is my rough draft code:

    Dim FromPath As String
    Dim ToPath As String
    Dim FileInFromFolder As Object
    Dim FileInToFolder As Object

    Dim fDialog As Office.FileDialog
    Dim FSO As Object

   ' Set up the Folder Dialog. '
    Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
    fDialog.InitialFileName = Application.CurrentProject.path
    With fDialog

    FromPath = "C:\Program Files\ALERT\Email Photos\"
    ToPath = "R:\" & Me.RepairsCorporateName & "\" &
Me.cboStoreNumber.Column(2) & "\" & Me.JobNumber & "\JobPhotos\"

   'Check to see if the JobPhotos folder exists, if not then create it
    If Dir(ToPath, vbDirectory) = "" Then
        MkDir ToPath
    End If

    Set FSO = CreateObject("scripting.filesystemobject")

    For Each FileInFromFolder In FSO.GetFolder(FromPath).Files
       ' Move each photo to new folder one at a time
        FileInFromFolder.Move ToPath
    Next FileInFromFolder

    End With




-- 
Jeff Barrows
MCP, MCAD, MCSD

Outbak Technologies, LLC
Racine, WI