Copy Files from One Folder to Another based on File Names in Access Table

[recyan]

Raw material :

1) 1st Folder on Desktop ( fldrData ) containing Word Documents ( eg. a.doc, b.doc, c.doc, d.doc ).


2) Access Table ( tblRequiredDocs ) with single field ( txtWantedDoc - PK ). Current values in this field are b & d ( separate records ).
3) 2nd Folder on Desktop ( fldrMissingData ), which is empty.

What needs to be done ?

The files from fldrData ( b.doc & d.doc - the values in tblRequiredDocs - b & d), need to be copied to fldrMissingData.

Thanks

[June7]

Open recordset, loop through recordset, use txtWantedDoc value to construct file paths.

Two ways to copy https://www.accessforums.net/access/...opy-45903.html

1. FileCopy

2. Windows API

[recyan]

Hi June,
Thanks for the pointers.
Will give it a go & keep you posted ( Might take a bit of time, with my great VBA skills ) .

Thanks

[recyan]

Ended up with this, with copy-paste from here & there ( It appears to be working ).

Function CopyDocs()
    
    Dim RequiredDocs As DAO.Database
    Dim rs As DAO.Recordset
    Dim strsql As String
    Dim GetDoc As String
    Dim GetDocFull As String
    Dim SourceA As String
    Dim Dest As String
    
    Set RequiredDocs = CurrentDb


    SourceA = "C:\myDesktop\fldrData\"
    Dest = "C:\myDesktop\fldrMissingData\"


    strsql = "SELECT * FROM tblRequiredDocs"
    Set rs = CurrentDb.OpenRecordset(strsql)


    'Check to see if the recordset actually contains rows
    If Not (rs.EOF And rs.BOF) Then
        rs.MoveFirst 'Unnecessary in this case, but still a good habit
        Do Until rs.EOF = True
            'Perform an edit
            rs.Edit
            GetDoc = rs("txtWantedDoc")
            GetDocFull = GetDoc & ".doc"


            FileCopy SourceA & GetDocFull, Dest & GetDocFull
            
            'Move to the next record. Don't ever forget to do this.
            rs.MoveNext
        Loop
    Else
        MsgBox "There are no records in the recordset."
    End If
    
    MsgBox "Finished looping through records."


    rs.Close 'Close the recordset
    Set rs = Nothing 'Clean up
End Function

Kindly let me know, if you find some pitfalls in it.

Thanks

[June7]

Don't think need rs.Edit.

Don't need GetDoc variable.

GetDocFull = rs!txtWantedDoc & ".doc"

For such a simple sql, I wouldn't bother with variable. Also, just pull the necessary field(s).

Set rs = CurrentDb.OpenRecordset("SELECT txtWantedDoc FROM tblRequiredDocs;")

Probably should move the finished looping message box to just under Loop. Don't want both message boxes to open if there are no records.

[recyan]

Don't think need rs.Edit.

Should have seen that.

Don't need GetDoc variable.
GetDocFull = rs!txtWantedDoc & ".doc"

Me & my tedious way of doing things.

For such a simple sql, I wouldn't bother with variable. Also, just pull the necessary field(s).

Set rs = CurrentDb.OpenRecordset("SELECT txtWantedDoc FROM tblRequiredDocs;")

Probably should move the finished looping message box to just under Loop. Don't want both message boxes to open if there are no records.

What was I doing with a *, when there is only one field in the table ?

Thanks a lot for the inputs.

[June7]

If there is only one field in table, then the wildcard is just fine, either way only one field is pulled. Except for the message box location, what you had didn't hurt anything. I just thought would be a little cleaner.

[recyan]

Thanks for the update.
Agree totally, the code sure has become cleaner.

Thanks