How do I move files with Access 2007 VBA?

What is the best way to run a parameter query, iterate through each record to acquire a file name and copy that file to a new location?

Here's what I have but it doesn't work runtime error no value given for one or more required parameters.

I'm just looking for a solution that will work.
Thanks so much!

-----------
'copy tiff image files to the batch folder
 Dim StrSQL As String
 Dim rst As ADODB.Recordset
 
 Set rst = New ADODB.Recordset
 
 StrSQL = "SELECT tblWWS_ATTACHMENTS.ATTACHMENT_FILE_NAME, tblBatch.BATCH_FOLDER_NAME FROM tblWWS_ATTACHMENTS INNER JOIN tblBatch ON tblWWS_ATTACHMENTS.BATCH_ID = tblBatch.BATCH_ID WHERE (((tblBatch.BATCH_FOLDER_NAME)= Folder));"

 'open the results read-only
  rst.Open StrSQL, CurrentProject.Connection
 
 If rst.RecordCount > 0 Then
 rst.MoveFirst
 Do While Not rst.EOF
 '....move file process
Dim fso
Dim file As String, sfol As String, dfol As String
file = ATTACHMENT_FILE_NAME ' change to match the file name
sfol = "C:\WWS_IMAGES" ' change to match the source folder path
dfol = "C:\WWS_BATCH\" & BATCH_FOLDER_NAME ' change to match the destination folder path
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(sfol & file) Then
    MsgBox sfol & file & " does not exist!", vbExclamation, "Source File Missing"
ElseIf Not fso.FileExists(dfol & file) Then
    fso.MoveFile (sfol & file), dfol
Else
    MsgBox dfol & file & " already exists!", vbExclamation, "Destination File Exists"
End If
 rst.MoveNext
 Loop
 End If

rst.Close
 
lblancatoAsked:
Who is Participating?
 
carsRSTCommented:
How do you get the variable in?

Might try something like this...


varFolder = "" 'put the folder you want to select

StrSQL = "SELECT tblWWS_ATTACHMENTS.ATTACHMENT_FILE_NAME, tblBatch.BATCH_FOLDER_NAME FROM tblWWS_ATTACHMENTS INNER JOIN tblBatch ON tblWWS_ATTACHMENTS.BATCH_ID = tblBatch.BATCH_ID WHERE (((tblBatch.BATCH_FOLDER_NAME)= '" & varFolder & "'));"

0
 
carsRSTCommented:
try this first...

StrSQL = "SELECT tblWWS_ATTACHMENTS.ATTACHMENT_FILE_NAME, tblBatch.BATCH_FOLDER_NAME FROM tblWWS_ATTACHMENTS INNER JOIN tblBatch ON tblWWS_ATTACHMENTS.BATCH_ID = tblBatch.BATCH_ID WHERE (((tblBatch.BATCH_FOLDER_NAME)= 'Folder'));"


If that doesn't work, then check your SQL field names in your SQL against the database field name.  Usually the error you get when one is off.
0
 
lblancatoAuthor Commented:
carsRST,
Folder is a varible. It has a value an this works when I manually run the query. That's the really weird thing about this. Once the SQL runs it has no record value.

Is there a better way to code for what I'm trying to accomplish????

Thanks.

0
 
lblancatoAuthor Commented:
Thanks so much, I going with a different approach.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.