Solved

FTP file upload from within Access

Posted on 2016-09-12
1
82 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 47

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

735 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