FTP file upload from within Access

I'm working on some code that I copied from Matthew V Carmichael from here.

My current code looks like:
Public Function FTP_SendOneFile(strPath As String, strfilename As String)
'=========================================================================
'FTP from Microsoft Access
'by Matthew V Carmichael
'Creates FTP Batch File, FTP command file (txt)
'Default directory location of files to upload/download is
'the same location as the mdb file that contains this module.
'Modified by Dale Fye on 9/12/2016
'========================================================================='
On Error GoTo Err_Trap

    Dim pFile As Long
    Dim ftpServer As String
    Dim strUserName As String
    Dim strPassword As String
    Dim ftpFolder As String
    Dim dtNow As Date

    If right(strPath, 1) <> "\" then strPath = strPath & "\"
    If Dir(strPath & strfilename) = "" Then
        MsgBox "File not found."
        Exit Function
    End If
    
    If Dir(strPath & "FTP_cmd.txt") <> "" Then Kill strPath & "FTP_cmd.txt"
    If Dir(strPath & "FTP_run.bat") <> "" Then Kill strPath & "FTP_run.bat"
    
    'FTP Server Settings
    ftpServer = "ftpserver"
    ftpFolder = "MyFolder"
    strUserName = "UserName"
    strPassword = "password"
    
    'Create text file containing FTP commands
    pFile = FreeFile
    Open strPath & "FTP_cmd.txt" For Output As pFile
    Print #pFile, "user" & " " & strUserName
    Print #pFile, strPassword
    Print #pFile, "cd " & Chr$(34) & ftpFolder & Chr$(34)
    Print #pFile, "Put " & Chr$(34) & strfilename & Chr$(34)
    'Use the Put command to upload, use the Get command to download.
    'Print #pFile, "Get " & "Your File Name"
    Print #pFile, "Pause"
    Print #pFile, "quit"
    Close pFile
    
    'Create batch file to execute FTP
    pFile = FreeFile
    Open strPath & "FTP_Run.bat" For Output As pFile
    Print #pFile, "ftp -n -s:FTP_cmd.txt" & " " & ftpServer
    Print #pFile, "Pause"
    Close pFile

    'Execute FTP command
    'Shell strPath & "FTP_Run.bat", 1
    
Err_Trap_Exit:
    Exit Function
    
Err_Trap:
    MsgBox Err.Number & " - " & Err.Description
    Resume Err_Trap_Exit

End Function

Open in new window

When I run this function I get an error message: Error opening script file FTP_cmd.txt  
But if I close the command window, select the FTP_Run.Bat file and select Open, the upload runs just fine.  I thought there might be a problem with timing, so I inserted a pause before the shell command, but that makes no difference, I still get the error message

The code in those two files looks like:
FTP_cmd.txt:
user Username
Password
cd "MyFolder"
Put "test file 2016_09_12_1400.txt"
Pause
quit

Open in new window

and FTP_Run.bat
ftp -n -s:FTP_cmd.txt ftpServer
Pause

Open in new window

Any ideas?
LVL 50
Dale FyeAsked:
Who is Participating?
 
Dale FyeConnect With a Mentor Author Commented:
The solution was to modify the Put statement to include the path that the file is being uploaded from, then modify the ftp command in the .bat file to include the path where the txt file resides.
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.