vba - Move Files to New Directory based on SQL Query Data

Question

Hoping someone could help:)

I have an MS Access table that holds file names along with other data Example:
AcctNum, FileName
12345, abc123.pdf

I have written a SQL to sort the file name based on account number. Example: Select * from tblTest where AcctNum='12345'

Files with these file names are located in a directory (C:Test<filename>.pdf)

I need to move the selected files based on the SQL I've written to move those files to a different directory (D:TestFromCode). Filename 'abc123.pdf' is located in C:Test move to D:est, loop through SQL results to move files based on criteria.

Attached is some VBA that I have written that I can get a file to copy from source to destination manually when I input the exact filename (as seen in the code, the file name is "abc123"), but I want to tie it to the mysql query recordset and move the group which will contain 1000's of files. Anyone know the best way I can tie this to recordset?

Appreciate any direction/help you provide.

Sub UpdateDirectory()

Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim mysource As String
Dim mydes As String
Dim mysql As String


mysql = "SELECT * from tblTest where ACCTNUM = 123456"
mysource = "C:Test"
mydes = "D:TestFromCode"


Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(mysource)
Set db = CurrentDb
Set rs = db.OpenRecordSet(mysql)


'I want to tie mysql recordset to below to move all files/records based 
'on ACCTNUM. This could result in moving 1000's of files to 
'destination.

 For Each objFile In objFolder.Files
     If InStr(1, objFile.Name, "abc123") > 0 Then
         Debug.Print "yes"
         objFSO.CopyFile objFile, mydes
     Else
         Debug.Print "no"
     End If
 Next

 'clean
Set objFolder = Nothing
 Set objFile = Nothing
Set objFSO = Nothing

End Sub

question from:https://stackoverflow.com/questions/65852716/move-files-to-new-directory-based-on-sql-query-data

Answer 1

Here is your code with a working answer: To be sure, @June7 is correct, but putting the answer in your context:

Sub UpdateDirectory()

Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim mysource As String
Dim mydes As String
Dim mysql As String


mysql = "SELECT * from tblTest where ACCTNUM = 123456"
mysource = "C:Test" 'Missing backslash - added later
mydes = "D:TestFromCode"


Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(mysource)
Set db = CurrentDb
Set rs = db.OpenRecordSet(mysql)


'I want to tie mysql recordset to below to move all files/records based 
'on ACCTNUM. This could result in moving 1000's of files to 
'destination.

While Not rs.EOF
    sourceFile = mysource & "" & rs!Filename 'Added a missing backslash
    destFile = mydes & rs!Filename

    If Dir(sourceFile) <> "" Then 'If sourceFile exists, then Dir(sourceFile) will return it's name, otherwise empty string
        FileCopy sourceFile, destFile 
    End If
    rs.MoveNext
Wend

End Sub