FTP Upload Code

[Subwind]

So, I am after an FTP upload code that works. I have tried a couple of versions, but they do not seem to work.

One of the codes, it does upload the file, but for some reason it sends it blank. But the local file has data, and when I use FileZilla, it sends the same file okay.

Function cmdFTPUpload()

'=========================================================================
'FTP from Microsoft Access
'by Matthew V Carmichael
'Craetes 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.
'========================================================================='

On Error GoTo Err_Trap
   
      Dim pFile As Long
      Dim strPath As String
      Dim strFileName As String
      Dim ftpServer As String
      Dim strUserName As String
      Dim strPassword As String

    'Create the file to transfer
     'DoCmd.TransferText acExportDelim, , "QUERY TO CREATE FILE", "FILE NAME.csv"

    'Path and Name of file to FTP
    strPath = "C:\"
     strFileName = "FILENAME.csv" 'Name of file to upload
    'FTP Server Settings


     ftpServer = "FTP SERVER"
     strUserName = "USER NAME"
     strPassword = "PASSWORD"

     'Create text file containing FTP commands
      pFile = FreeFile
      Open strPath & "FTP_cmd.txt" For Output As pFile
      Print #pFile, "user"
      Print #pFile, strUserName
      Print #pFile, strPassword
      Print #pFile, "Put " & strPath & strFileName
      Print #pFile, "quit"
      Close pFile
      
      'Create batch file to execute FTP
      pFile = FreeFile
      Open strPath & "FTP_Run.bat" For Output As pFile
      Print #pFile, "CD """ & strPath & """"
      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 

The above code always says failed, but it does upload a blank file...

PS: In the cmd screen, it says Error 426 Transfer Failed, but it does send a .csv file with the same name, but it has no data in it (0 bytes)

~Matt

[ItsMe]

So something in your batch file is not working correctly. I don't see anything in your VBA that would invoke a file transfer using FTP over a network.

[Subwind]

So something in your batch file is not working correctly. I don't see anything in your VBA that would invoke a file transfer using FTP over a network.

I believe the vba creates the batch file, and runs it. As I said, it does upload a file, but uploads it blank.

http://www.databaseadvisors.com/news...thinaccess.asp

This works though, so I need to find a way to automate this into my code....

[ItsMe]

One of the first things mentioned in that link is

"To use a FTP client you’ll need a good understanding of the API functions within the ‘wininet.dll’."

You say that there is a file being created and it is blank. I suppose this would indicate that something is working correctly. The VBA you show here does not indicate anything is being created over a network. So I can not start to guess what is wrong what is correct, etc.

You are showing code for a function. There will need to be additional code. creating a file in a C drive does not translate to file transfer. Where is your final resulting file being created? Is it on a network FTP server? Maybe the protocol isn't matching. There are different types of FTP. There are secure types there are simple, more than I can remember.... So the server will need to match the specific File Transfer Protocol (protocol), so to speak

[Subwind]

One of the first things mentioned in that link is

"To use a FTP client you’ll need a good understanding of the API functions within the ‘wininet.dll’."

You say that there is a file being created and it is blank. I suppose this would indicate that something is working correctly. The VBA you show here does not indicate anything is being created over a network. So I can not start to guess what is wrong what is correct, etc.

You are showing code for a function. There will need to be additional code. creating a file in a C drive does not translate to file transfer. Where is your final resulting file being created? Is it on a network FTP server? Maybe the protocol isn't matching. There are different types of FTP. There are secure types there are simple, more than I can remember.... So the server will need to match the specific File Transfer Protocol (protocol), so to speak

DoCmd.TransferText acExportDelim, , "QUERY TO CREATE FILE", "FILE NAME.csv"

That part above creates my csv file to transfer. The file being created is not blank. And uploading it using either an FTP program, or the second link I sent, it sends as is. Using the first code I have posted, it sends the file, but removes all data from it.

The link from where I found the first code, alot of people are using that code without issues, so it's something my end.

[ItsMe]

I think I see the issue. The file you want to upload does not exist in the same directory as the batch file. The code shares strPath for all three files - the third file being the CSV you are trying to upload.

Two things. First, be sure to save the csv file to the correct directory. Second, there is a typo in the file name for the CSV.

Change your code
DoCmd.TransferText acExportDelim, , "QUERY TO CREATE FILE", "FILE NAME.csv"

to
DoCmd.TransferText acExportDelim, , "QUERY TO CREATE FILE", "FILENAME.csv"

[Subwind]

I think I see the issue. The file you want to upload does not exist in the same directory as the batch file. The code shares strPath for all three files - the third file being the CSV you are trying to upload.

Two things. First, be sure to save the csv file to the correct directory. Second, there is a typo in the file name for the CSV.

Change your code
DoCmd.TransferText acExportDelim, , "QUERY TO CREATE FILE", "FILE NAME.csv"

to
DoCmd.TransferText acExportDelim, , "QUERY TO CREATE FILE", "FILENAME.csv"

The file being saved goes into C:\, the batch file is created in C:\. Also, my file is not called "FILE NAME". I was using that as a statement to show the place where I named my file. Sorry, I am not very good at explaining things. I will use the second example I posted, and code that into my database as I know that works. Strange why the first one doesn't work. I think it may be a timing issue, the command being ended before file has uploaded.

[ItsMe]

Maybe you can place some test files in a folder and run the batch file. One line.

Shell strPath & "FTP_Run.bat", 1

Also, maybe I would be sure to use a subfolder instead of running it from the root of the drive. Break down the code and test it one stage at a time.