• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1303
  • Last Modified:

MSACCESS and FTP Commands... How To..

I need a onclick event to do the following

Filename = orderid
Open a FTP location i.e. ftp://website.com
Login username
login password
go to path
check if file exits orderid&".xml"
if exisits then download to local path d:\download
import xml file in to database as table
if not exist then close ftp and
msgbox "File does not exisit" <-- in access
end

Thats it..

Now I know I can creat a batch file that will run a .scr <scrtipt> file that can login to ftp etc.  but how do i pass a variable from access form to a batch file....  

instead of running a batch file from outside access, doesn't access have a way to access ftp itself?

0
Ricky11
Asked:
Ricky11
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Now I know I can creat a batch file that will run a .scr <scrtipt> file that can login to ftp etc.  but how do i pass a variable from access form to a batch file....   >>

  You create the file on disk, then execute it with the shell command. ie. something like the function below.

<<instead of running a batch file from outside access, doesn't access have a way to access ftp itself?>>

   Yes. See the code here:

http://www.mvps.org/access/modules/mdl0037.htm

  This is a FTP class that you can drop into Access and use without shelling out to the command line.  This is the route I would go.

JimD

Function FTPUploadFile(strLocalFileName As String, strFTPFilename As String, strFTPSiteName As String, strUsername As String, strPassword As String, Optional strTransferType As String) As Boolean
       
        ' Procedure to upload file to FTP site.
        ' Sends e-mail to ITALERT if upload fails and returns false.
       
        Const RoutineName = "FTPUploadFile"
        Const Version = "1.1"
       
        Dim strFTPCommandFile As String
        Dim strFTPScriptFile As String
        Dim strFTPLogfile As String

        Dim lngHWnd As Long
        Dim intFileNum As Integer
        Dim strMailMessage As String
        Dim oXYR_SendMail As New XYR_SendMail

10      On Error GoTo FTPUploadFile_Error
       
        ' Generate file names
20      strFTPCommandFile = "\FTP_" & AppShortName() & ".txt"
30      strFTPScriptFile = "\FTP_" & AppShortName() & ".bat"
40      strFTPLogfile = "\FTP_" & AppShortName() & ".Log"
       
        ' Write command file
50      Open strFTPCommandFile For Output As #99
60      Print #99, strUsername
70      Print #99, strPassword
80      Print #99, "type " & IIf(strTransferType = "B", "binary", "ascii")
90      Print #99, "put " & Chr$(34) & strLocalFileName & Chr$(34) & " " & strFTPFilename
100     Print #99, "quit"
110     Close #99

        ' Write script file
120     Open strFTPScriptFile For Output As #99
130     Print #99, "@ftp -i -s:" & strFTPCommandFile & " " & strFTPSiteName & " > " & strFTPLogfile
140     Close #99

        ' Execute
150     lngHWnd = Shell(strFTPScriptFile, vbHide)
160     WaitWhileRunning (lngHWnd)

        ' Check log file
170     If IsValidFTP(strFTPLogfile) Then
180       FTPUploadFile = True
190     Else
200       If DebugMode() = True Then
210         Stop
220         FTPUploadFile = False
230       Else
240         oXYR_SendMail.SetParams "ITALERT", ".", "."
250         oXYR_SendMail.Subject = "FTP Upload failed"
260         strMailMessage = "The file: " & strLocalFileName & " did not upload to the FTP site: " & strFTPSiteName & " with username: " & strUsername & " password: " & strPassword & vbCrLf
270         strMailMessage = strMailMessage & "Command, script, and log files are attached." & vbCrLf & vbCrLf
280         strMailMessage = strMailMessage & "App name:" & AppShortName() & " Version: " & AppVersion()
290         oXYR_SendMail.Message = strMailMessage
300         oXYR_SendMail.Attachment = Dir(strFTPCommandFile) & ";" & Dir(strFTPScriptFile) & ";" & Dir(strFTPLogfile)
310         oXYR_SendMail.Send
320         FTPUploadFile = False
330       End If
340     End If

FTPUploadFile_Exit:
350     On Error Resume Next
       
360     If Dir(strFTPCommandFile) <> "" Then Kill strFTPCommandFile
370     If Dir(strFTPScriptFile) <> "" Then Kill strFTPScriptFile
380     If Dir(strFTPLogfile) <> "" Then Kill strFTPLogfile
       
390     Close #99
       
400     Exit Function

FTPUploadFile_Error:
410     UnexpectedError ModuleName, RoutineName, Version, Err.Number, Err.Description, Err.Source, VBA.Erl
420     FTPUploadFile = False
430     Resume FTPUploadFile_Exit
       
End Function
0
 
Ricky11Author Commented:
tks.. i just shelled out and ran a macro.. thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now