MSACCESS and FTP Commands... How To..

Posted on 2007-10-16
Last Modified: 2013-12-02
I need a onclick event to do the following

Filename = orderid
Open a FTP location i.e.
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

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?

Question by:Ricky11
    LVL 56

    Accepted Solution

    <<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:

      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.


    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

    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

    410     UnexpectedError ModuleName, RoutineName, Version, Err.Number, Err.Description, Err.Source, VBA.Erl
    420     FTPUploadFile = False
    430     Resume FTPUploadFile_Exit
    End Function

    Author Comment

    tks.. i just shelled out and ran a macro.. thanks.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now