Avatar of jonlake
jonlake
Flag for Guernsey asked on

Move files based on MS Access query match

Hi, I need to move and rename PDF files if the existing filename matches a database query. My query has a result called case_num. Where the case_num matched the name of a PDF file in a known folder, I want to move it to another location and add the suffix "_original", the .pdf.

I've been struggling with this for a little while now so your help would be most appreciated.

Regards, Jon
Microsoft AccessVisual Basic Classic

Avatar of undefined
Last Comment
Nick67

8/22/2022 - Mon
Nick67

VBA is good?

'pseudocode
'fire up a recordset of the existing filenames
'fire up a FilesystemObject
'walkdown the recordset
Do until rs.EOF
   if fs.FileExists(Path & rs!filename) then
         fs.movefile Path & rs!filename, newPathAndFileName
   End if
   rs.movenext
Loop

That's the idea, anyway.
Now, you haven't really given me enough detail to flesh more than pseudocode
Is it the logic you are struggling with, the syntax, or the whole ball of wax?
jonlake

ASKER
Hi Nick, the query (qryCase_File_Docs) has a field called case_file_num. In a folder location (e.g. C:\temp\case_files\) there are several hundred PDF files, each of which will have a name which matches a case_file_num result in the query.

I want to loop through the query and where a file exists of the same name, move it to another folder (e.g. c:\temp\case_files\renamed\), with a new file name, for example:

File c:\temp\case_file_num\123456.pdf would become c:\temp\case_file_num\newfile\123456_original.pdf

I hope this is enough to work with, thanks.
Nick67

is the value of [case_file_num] = 123456
or 123456.pdf
Your help has saved me hundreds of hours of internet surfing.
fblack61
Nick67

This will be very close to production code

Dim db As Database
Set db = CurrentDb

Dim rs As Recordset
Set rs = db.OpenRecordset("select * from qryCase_File_Docs;", dbOpenDynaset, dbSeeChanges)

Dim fs As Object 'our friend the filesystemobject
Set fs = CreateObject("Scripting.FileSystemObject")

Dim BuiltPath As String
Dim FinalPath As String

BuiltPath = "C:\temp\case_files\"
If fs.FolderExists(BuiltPath) = False Then
    MsgBox "The Source folder doesn't exist!"
    Exit Sub
End If

FinalPath = "c:\temp\case_files\newfile\"
If fs.FolderExists(FinalPath) = False Then
    MsgBox "The Destination folder doesn't exist!"
    Exit Sub
End If

'the loop
Do Until rs.EOF
    If fs.FileExists(BuiltPath & rs!case_file_num & ".pdf") Then 'does it exist
        fs.MoveFile BuiltPath & rs!case_file_num & ".pdf", FinalPath & rs!case_file_num & "_original.pdf" 'move it
    End If
    rs.MoveNext 'next record
Loop 'rinse and repeat

MsgBox "Done!"

Open in new window

ASKER CERTIFIED SOLUTION
Nick67

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
jonlake

ASKER
Superb! Completely nailed it!

I've built it, tested it and it's working perfectly. It should only ever be a one-off each time so checking if a file already exists won't (shouldn't) be a problem. Thank you, thank you......clean and elegant solution.
Nick67

Glad you liked it!
Hopefully, it'll be useful going forward.
It's always nice to know 'hey I've coded something like that before!'

Nick67
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.