Export Data to FTP Site 1

[bgreen]

Is it possible to transfer data from an Access database to a flat file on an ftp site?

I have been able to create a file on a lan drive using TransferText Action. But I have yet to be able to figure out how to do the same to a location on the ftp server? Any suggestions?

 

[Remou]

A search using Google (above) will return a number of threads and this FAQ:
How do I FTP files to a remote server in MS Access
faq705-5904

The code below is a very minor modification of code by strongm (MIS)
thread222-1324805, 24 Jan 07 12:06
All acknowledgements to stongm

Private Sub Command2_Click()
'Reference: Microsoft Shell Controls and Automation
    Dim ftpFolder As Shell32.Folder
    Dim myShell As Shell32.Shell
    
    Set myShell = New Shell
    
    Set ftpFolder = myShell.NameSpace("ftp://user:password@ftp.site.com")
    ftpFolder.CopyHere "c:\test.txt"
    
End Sub

 

[PHV]

As I like animations:
ftpFolder.CopyHere "c:\test.txt"[!], 16[/!]

 

[bgreen]

I am extremely new to access. I read the link and I am unsure where to put the code. Could you provide some insight? I have a form that enters data into a database. The button will be used to create a tilde delimited file which I want to place on my ftp site.

 

[Remou]

Cut the code out of the sub above and put it after the code that creates your delimited file. You may need a pause or check to make sure your file has bee created. You will need to edit the code to show the name of your site and your log on details. Please be careful and check everything in parts before running unattended.

 

[bgreen]

Here's my issue with that suggestion... The button is using a macro. The macro is created not by VBA code it's using the macro design window. Do you suggest changing this to code?

Or do you think there is a way I could do this without the code?

 

[bgreen]

Could I create a module and select an action to call the module function or procedure?

 

[Remou]

You could create a function or you could save the macro as code and add to that. There is no day like today for changing to code.

If you create a function in a module, make sure that you do not give the function and the module the same name. Also, test each stage separately.

 

[bgreen]

Okay. I converted the macro to vb code. Now it is a module. And in the module a function was created:

Code:

Function mcrExport()
On Error GoTo mcrExport_Err

DoCmd.Echo False, ""
' Print Export Report
DoCmd.OpenReport "rptAuditReport", acViewNormal, "", "", acNormal
' Update Export Table (tblExportData)
DoCmd.OpenQuery "qryUpdateExportDataTable", acViewNormal, acEdit
' Create export text file to S:\Finance\Common\Brent
DoCmd.TransferText acExportDelim, "CLIC Tilde Export Specification", "tblExportData", "S:\Finance\Common\Brent\CHQ" & Format(Now(), "yymmddhhnnss") & "UPLOAD" & ".txt", False, ""
' Update History Table (tblHistory)
DoCmd.OpenQuery "qryAppendHistory", acViewNormal, acEdit
' Delete data in tblData
DoCmd.OpenQuery "qryDelete tblData", acViewNormal, acEdit
' Delete date in tblExportData
DoCmd.OpenQuery "qryDelete tblExportData", acViewNormal, acEdit
Beep
MsgBox "Export completed.", vbInformation, ""


mcrExport_Exit:
Exit Function

mcrExport_Err:
MsgBox Error$
Resume mcrExport_Exit

End Function

I want to transfer the file "S:\Finance\Common\Brent\CHQ" & Format(Now(), "yymmddhhnnss") & "UPLOAD" & ".txt" to my ftp site (ie. ftp01) at location lifecomm/transfer. I am still unsure about a few things.

1. How do I call this modules function instead of the prior macro, which is referenced in the properties of a command buttons (Command1) 'OnClick' action?

2. Do I create a new macro to call the module? How do I do that?

 

[bgreen]

I figured out how to call the code. I now need a little help to get the file to my ftp site.

 

[Remou]

Ok. What you have above should be called a sub, because functions return a result and the above code does not, but we'll leave that for now.

First, put the FTP code in a sub underneath the above function, call it something reasonable, say, FTPFile, and get rid of the word 'Private'. Next, you need to add a click event to the Command1 button. You can do this by choosing [Event Procedure] for On Click (properties page, event tab) and then clicking the three dots to the right. This should bring you to the code page, where you should see:

[tt]Private Sub Command1_Click()

End Sub[/tt]

You need to add a bit to this event:

[tt]Private Sub Command1_Click()
'Create file
Call mcrExport
'FTP file
Call FTPFile
End Sub[/tt]

Don't forget to edit FTPFile to show your FTP details.

 

[bgreen]

I tried this and it did not work. Here is what I have, maybe you could provide some insight.

Private Sub Command37_Click()
On Error GoTo Err_Command37_Click

Dim sResult As String, sFile As String
Dim sSVR As String, sFLD As String
Dim sUID As String, sPWD As String
Dim sLocalFLD As String

Call fncFileExport

sFLD = ("lifecomm\ClicFtp\Finance\CLIC\DEVL\INBOUND\AccessFTP")
sFile = ("CHQ" & Format(Now(), "yymmddhhnnss") & "UPLOAD" & ".txt")
sLocalFLD = ("S:\Finance\Common\Brent")
sSVR = ("winftp01")
sUID = ("finftp")
sPWD = ("finftp")

MsgBox "Parameters " & sFile, vbInformation, "INFO"


If sSVR = "" Then
MsgBox "Invalid server.", vbExclamation, "E R R O R"
Else

sResult = UploadFTPFile(sFile, sSVR, sFLD, sUID, sPWD)

End If

Exit_Command37_Click:
Exit Sub

Err_Command37_Click:
MsgBox Err.Description
Resume Exit_Command37_Click

End Sub

---------------------------

Public Function UploadFTPFile(sFile As String, sSVR As String, sFLD As String, sUID As String, sPWD As String) As String

Dim sLocalFLD As String
Dim sScrFile As String
Dim sSource As String
Dim iFile As Integer
Dim sExe As String

Const q As String * 1 = """"

On Error GoTo Err_Handler
sLocalFLD = CurrentProject.Path & "\" & sFLD

' will break if empty folder exist so error to pass
' must create folder first, so API calls work
On Error Resume Next
If Dir(sLocalFLD & "\") = "" Then MkDir (sLocalFLD)
On Error GoTo Err_Handler

sSource = q & sLocalFLD & "\" & sFile & q
sScrFile = sLocalFLD & "\upload.scr"
If Dir(sScrFile) <> "" Then Kill sScrFile

' Open a new text file to hold the FTP script and load it with
' the appropriate commands. (Thanks Dev Ashish !!!)
iFile = FreeFile
Open sScrFile For Output As iFile
Print #iFile, "open " & sSVR
Print #iFile, sUID
Print #iFile, sPWD
Print #iFile, "cd " & sFLD
Print #iFile, "binary"
Print #iFile, "lcd " & q & sLocalFLD & q
Print #iFile, "put " & sSource
Print #iFile, "bye"
Close #iFile

sExe = Environ$("COMSPEC")
sExe = Left$(sExe, Len(sExe) - Len(Dir(sExe)))
sExe = sExe & "ftp.exe -s:" & q & sScrFile & q

' ShellWait sExe, vbHide
DoEvents

Exit_Here:
UploadFTPFile = GetFileText(sScrFile)
DoCmd.Hourglass False
Exit Function
Err_Handler:
MsgBox Err.Description, vbExclamation, "E R R O R"
Resume Exit_Here
End Function