How to use FileSystemObject work in Access 2007

Okay why won't the FileSystemObject work in my Access 2007?
I get a runtime error 58 "File already exists" on this line:
 fso.MoveFile (sfol & File), dfol

What am I missing???
Here's my code any advice would be appreciated.
Thanks,

Public Sub MoveFiles()

Dim MyDB As Database
Dim MyRS As Recordset

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMoveFiles")

MyRS.MoveFirst

If MyRS.RecordCount > 0 Then
 MyRS.MoveFirst
 Do While Not MyRS.EOF
 '....move file process

Dim fso As Object
Dim File As String, sfol As String, dfol As String

File = MyRS!ATTACHMENT_FILE_NAME ' change to match the file name
sfol = "C:\IMAGES" & "\" ' change to match the source folder path
dfol = "C:\BATCH" & "\" & MyRS!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
 MyRS.MoveNext
 Loop
 End If

MyRS.Close

End Sub
lblancatoAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
File = MyRS!ATTACHMENT_FILE_NAME ' change to match the file name
sfol = "C:\IMAGES" & "\" ' change to match the source folder path

'Change this
'dfol = "C:\BATCH" & "\" & MyRS!BATCH_FOLDER_NAME  ' change to match the destination folder path

'to
dfol = "C:\BATCH" & "\" & MyRS!BATCH_FOLDER_NAME  & "\"

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

    'change this
    'fso.MoveFile (sfol & File), dfol
     'to
    fso.MoveFile (sfol & File), dfol & File
Else
    MsgBox dfol & File & " already exists!", vbExclamation, "Destination File Exists"
End If




0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The obvious answer would be that the file specificed by your path does not exist. Can you Debug.Print the fully formed variable you're using? To do that, just insert this line after setting the sfol and dfol values:

Debug.Print sfol & File

This will print to the Immediate window - examine that to determine if you are correctly building that file path.
0
 
lblancatoAuthor Commented:
The file reference appears to be correct.
from the Immediate Window:
C:\WWS_IMAGES\STM010837BACKFILE.TIF

Could it be a reference library issue????

Thanks.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
lblancatoAuthor Commented:
I have even tried
fso.MoveFile "C:\IMAGES\837E.PDF", "C:\BATCH\BackUp10"
 the source and destination into the code and still receive the same error  runtime error 58 "File already exists". This is just crazy.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Or just do this:

If Dir("Your Full Path Here") = "" Then
  FileCopy "source", "Destination"
End If
0
 
lblancatoAuthor Commented:
You are a genius. Thank you, thank you, thank you.
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.