Solved

FTP file upload from within Access

Posted on 2016-09-12
1
88 Views
Last Modified: 2016-09-12
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?
0
Comment
Question by:Dale Fye (Access MVP)
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 48

Accepted Solution

by:
Dale Fye (Access MVP) earned 0 total points
ID: 41795295
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

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article provides a convenient collection of links to Microsoft provided Security Patches for operating systems that have reached their End of Life support cycle. Included operating systems covered by this article are Windows XP,  Windows Server…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question