Automating FTP in Access 2010 (1 Viewer)

[centfury7]

Hi, I'm trying to come up with a way to FTP a file from mainframe into Access. So far I've got the below code. My question is how to I get the text file to land in C:\temp\ folder. Every time I execute this script the destination file always lands in some random folder. Below is the code I used.

Dim TextFile As Integer
Dim FilePath As String

'What is the file path and name for the new text file?
'ftpServer = "server"

'What is the file path and name for the new text file?
FilePath = "C:\temp"

'Determine the next file number available for use by the FileOpen function
TextFile = FreeFile

'Open the text file
Open FilePath & "trfr.txt" For Output As TextFile

'Write some lines of text
Print #TextFile, "open uhc1s1ip"
Print #TextFile, "username"
Print #TextFile, "password"
Print #TextFile, ""
Print #TextFile, "get 'T0003.CCT.M1702.CAMS.MEMBER.TAGIDX.BL01' MEMBER_DEMOGRAPHIC_BL.TXT"
Print #TextFile, ""
Print #TextFile, "close"
Print #TextFile, "bye"

'Save & Close Text File
Close TextFile

Set objShell = CreateObject("WScript.Shell")
objShell.Run "%COMSPEC% /k FTP -i -s:" & "C:\temp\trfr.txt", 1, True

 

[Frothingslosh]

This link may be what you're looking for, especially the procedure provided toward the end:

http://www.databasejournal.com/feat.../Simple-FTP-Methods-from-Microsoft-Access.htm

 

[centfury7]

I've been trying to use that code and still can't get the file to download to the C:/temp location.

 

[Frothingslosh]

At no point are you telling it to download it to C:\Temp. The syntax is

get <SourcePath> <TargetPath>

You're telling it to grab T0003.CCT.M1702.CAMS.MEMBER.TAGIDX.BL01 and save it to the location MEMBER_DEMOGRAPHIC_BL.TXT.

If you want it saved to C:\Temp\MEMBER_DEMOGRAPHIC_BL.TXT, then, assuming you're copying BL01 as Member_Demographic_BL.txt you need to tell it so:

Print #TextFile, "get " & Chr(34) & "T0003.CCT.M1702.CAMS.MEMBER.TAGIDX.BL01" & Chr(34) & " " & Chr(34) & FilePath & "\MEMBER_DEMOGRAPHIC_BL.TXT" & Chr(34)

 

[centfury7]

That worked! Thank you!

 

[Frothingslosh]

Glad you didn't trip over the space I accidentally left out after 'get'.

 

[centfury7]

One other question. Do you know how I can get command prompt to close when after the ftp is completed?

 

[Gasman]

One other question. Do you know how I can get command prompt to close when after the ftp is completed?

exit closes a cmd window when used interactively?