ms-accessvba

Moving Files in MSOffice Access 2010


I am attempting to create a button on one of my forms in Access that will move a file from one folder to another. The filepath of the item is stored in the database. My current approach is using VB and is displayed here.

Private Sub Command21_Click()
    Dim d As Database
    Dim r As Recordset
    Dim path As Field
    Dim fromPath As String
    Dim toPath As String
    Set d = CurrentDb()
    Set r = d.OpenRecordset("Documents")
    Set path = r.Fields("Action Items Location")
    While Not r.EOF
        fromPath = path
        Set toPath = My.Computer.FileSystem.GetParentPath(fromPath) 'Error line
        toPath = toPath & "\to folder"
        My.Computer.FileSystem.MoveFile fromPath, toPath
    Wend

End Sub

I keep getting an error saying object required on the line marked Error line. How do I fix this error, or am I even going about it the correct way?


Solution

  • Thanks for the replies, though after a bit more research, and the suggestion of @Basdwarf, I was able to find a solution. Here's the finished code

    Private Sub Command21_Click()
        Dim d As Database
        Dim r As Recordset
        Dim path As Field
    
        Dim fromPath As String
        Dim toPath As String
        Dim fileName As String
    
        Dim filesystem As Object
    
        Set filesystem = CreateObject("Scripting.FilesystemObject")
        Set d = CurrentDb()
        Set r = d.OpenRecordset("Documents")
        Set path = r.Fields("Action Items Location")
    
        fromPath = path
        fileName = filesystem.GetFileName(path)
        toPath = filesystem.GetParentFolderName(filesystem.GetParentFolderName(fromPath)) & "\to folder" & "\" & fileName
        MsgBox (fromPath)
        MsgBox (toPath)
        FileCopy fromPath, toPath
        Kill fromPath
    End Sub