Control External Processes using Scripts - FTP

[NeoPa]

Introduction.

A number of people have asked me if there's a way of controlling external processes from within Access. By external processes I'm talking about running scripts outside of Access. That would include BAT, CMD & PowerShell scripts, and even FTP scripts using Windows' FTP.EXE command line interface.

The power of these scripting interfaces is hard to put a limit on. FTP.EXE, in particular though, gives control of sending and receiving files across the internet to various FTP sites. Most web sites are updated using this interface. It's possible to automate that in Access.

One of my favourite uses of this type of scripting is for upgrading the current version of a project. I update front-end databases automatically when started, and also give an option to upgrade later at any time in case of corruption (Something Access databases are somewhat prone to unfortunately).

For this article though, I'll concentrate on the FTP script. If I post another one later on the upgrade process I'll link it, but the attachment includes code to do both in case anyone's interested to look and see. If you particularly want the article on the Upgrade process then please PM me. The more PMs I get, the higher up my priority list it will go ;-)

That's what I'll be explaining in this article. Another, in the same subject area, is Control External Processes using Scripts - CMD.
For those who would find it easier to see this explained and shown in a video first, please visit (Video) Controlling External Processes in Access - FTP and maybe finish reading this afterwards for a fuller explanation.


Overall Concept.

This concept relies on a table which, at its simplest, is designed as :

Table=[tblCMD]

Field Name  Type        PK (Compound)
Template    Boolean     #1
Type        String(1)   #2
LineNo      Long        #3
Cmd         String(255)

In certain circumstances, and I come across that during my database upgrade CMD script, it's possible for the length of the command in a single line to exceed the 255 characters allowed. So, in my attached example, I use two fields [Cmd1] & [Cmd2] in place of the single [Cmd].

One very important point to remember, when dealing with the data to be entered into this table for your scripts, is that extra power comes from the ability to have parameters in the data which are replaceable. Without that, you may as well simply use saved script files. In my example I use the percent (%) followed by two alphabetic characters to mark the points in the data where I want to insert values from the code. An example for the FTP script is %Ac, which is used for the name of the account. This is used differently for the upgrade CMD script so beware of confusing the two. Here is a list of the upgrade CMD script replacements as well as the values used for the FTP script. Replacement parameters have the same name as their related variables except % replaces str. So, strBa in the code is used to replace occurrences of %Ba in the data.

The replacement parameters for the FTP script are %FS (FTP Server), %Ac (Account Name) & %PW (Password).

The next step is to create an updated copy of the template for the particular script in the same table. This new data is recognisable because the [Template] value is False. The updates are to replace the parameters mentioned earlier with their required values. This new data (only) is then exported to the script file via a query, Once exported this data is then deleted. Export specifications are necessary in order to create the script files correctly from the data.

Once all that's done and the script file is ready we need to invoke it.


Example Data.

Table=[tblCMD]

Template  Type  Order  Cmd                            Template  Type  Order  Cmd
  TRUE      F     10   Open %FS                         FALSE     F     10   Open FTP.AccessConsultantUK.co.uk
  TRUE      F     20   %Ac                              FALSE     F     20   ACUKdemo
  TRUE      F     30   %PW                              FALSE     F     30   PublicPW
  TRUE      F     40   binary                           FALSE     F     40   binary
  TRUE      F     50   mkdir test                       FALSE     F     50   mkdir test
  TRUE      F     60   cd test                          FALSE     F     60   cd test
  TRUE      F     70   send ACLogo.JPG sent.file        FALSE     F     70   send ACLogo.JPG sent.file
  TRUE      F     80   dir                              FALSE     F     80   dir
  TRUE      F     90   rename sent.file renamed.file    FALSE     F     90   rename sent.file renamed.file
  TRUE      F    100   dir                              FALSE     F     100  dir
  TRUE      F    110   get renamed.file BackAgain.JPG   FALSE     F     110  get renamed.file BackAgain.JPG
  TRUE      F    120   delete renamed.file              FALSE     F     120  delete renamed.file
  TRUE      F    130   rmdir test                       FALSE     F     130  rmdir test
  TRUE      F    140   dir                              FALSE     F     140  dir
  TRUE      F    150   bye                              FALSE     F     150  bye

NB. The data on the left is the original template data. The data on the right is the updated data and that's what's exported to create the actual script file.

This script is mainly to illustrate some of the commands available within FTP.EXE. The same file is moved around and renamed to demo some possibilities. To see more of what it can do for you type FTP on a CMD command line, to start FTP, then type ? for a list of available commands. Type HELP {command} to see more detail for any particular command. When done type bye to terminate the FTP session.


Code.

While some of the lines of code I show will refer to some of my own routines, this is a very small part of the code and should be very obvious what it's doing even if the code isn't shown. So, I'll show and explain the main code and include the whole database in the attachment so any other routines may be explored if desired. My code can always be reused if required. The only thing I claim is copyright. Others can use and change the code freely.

I'll start by explaining that MultiReplace() is one of my functions and that it simply extends the VBA.Replace() function by allowing multiple pairs of from and to replacements. I don't use this in the SQL though, as you'll see from the clumsy multi-use of the Replace() function calls in lines #16 through #19. This is where the parameters are replaced. To see the effect of this put a breakpoint on line #41 and print the value of strSQL at that point in the code.

I've included the code for SetStrings() below, but it's basically used (here) for getting the folder that we're running the project from.

• Lines #10 & #11 set the names of the files to use.
• Lines #42 & #43 clear up any data leftover previously then add the new data using the SQL just prepared.
• Lines #44 through #49 delete the file first if it exists then exports our newly prepared data to the file.
  DoCmd.TransferText() fails if the file extension isn't TXT, or at least something it recognises as text. So, we have to create the file with that name and then rename it.
• Lines #50 & #51 rename the file safely.
• Line #52 clears the new data from [tblCMD].
• Lines #53 through #58 handle prompting the user.
• Lines #59 through #64 execute the FTP script file using the START command of CMD.EXE. It also uses the procedure ShellWait(), which can be found at ShellWait() Function. This is obviously included in the attachment too.
  For help on CMD.EXE type HELP CMD on a CMD command line. For help using START type START /? on a CMD command line (Please ignore where it says that the "title" is an optional parameter. It isn't. Getting past that one wasted a number of hours for me).
  The use of &&PAUSE at the end of the line is purely for demo purposes. It allows the operator to see the log of what's happened before hitting any key on the keyboard when the window will close and return to the Access project.
• NB. Line #60 uses single-quotes (') to represent double-quotes (") within a VBA string. These are amended to the (correct) double-quotes in line #62 though. Part of the same code.

'TestFTP() creates and runs the script file to test FTP.
Public Function TestFTP() As Boolean
    Dim strAccount As String, strFTPServer As String, strFile As String
    Dim strTemp As String, strSQL As String, strMsg As String, strCmd As String
    Dim dbVar As DAO.Database
 
    On Error GoTo ErrorHandler
    strMode = SwitchMode(strType:="Process")
    Call SetStrings
    strFile = strFo & "\SCRIPT.FTP"
    strTemp = strFo & "\SCRIPTFTP.Txt"
    'Before we go any further, and regardless of whether or not we run the
    '  script, let's clear away any existing copy of SCRIPTFTP.Txt.
    '  It's checked again immediately prior to being created.
    If Exist(strTemp) Then Call KillFile(strTemp)
    strCmd = "Replace(Nz([~C],''),'%FS','%sFS')"
    strCmd = Replace("Replace(%C,'%Ac','%sAc')", "%C", strCmd)
    strCmd = Replace("Replace(%C,'%PW','%sPW')", "%C", strCmd)
    strCmd = MultiReplace(strCmd, "%sFS", conFTPServer _
                                , "%sAc", conFTPAccount _
                                , "%sPW", Scramble(conFTPPW))
    strSQL = "INSERT INTO [tblCMD]%L" _
           & "      ( [Template]%L" _
           & "      , [Type]%L" _
           & "      , [Order]%L" _
           & "      , [Cmd1]%L" _
           & "      , [Cmd2])%L" _
           & "SELECT  False AS [Template]%L" _
           & "      , [Type]%L" _
           & "      , [Order]%L" _
           & "      , [C1] AS [Cmd1]%L" _
           & "      , Null AS [Cmd2]%L" _
           & "FROM    (SELECT [Type]%L" _
           & "              , [Order]%L" _
           & "              , %C1 AS [C1]%L" _
           & "         FROM   [tblCMD]%L" _
           & "         WHERE  ([Template])%L" _
           & "           AND  ([Type]='F')) AS [qC]"
    strSQL = MultiReplace(strSQL, "%C1", Replace(strCmd, "~C", "Cmd1") _
                                , "%L", vbNewLine)
    Set dbVar = CurrentDb()
    Call ClearTable(strTable:="tblCMD", strWhere:=conClearCMD)
    Call dbVar.Execute(Query:=strSQL, Options:=dbFailOnError)
    If Exist(strTemp) Then Call KillFile(strTemp)
    Call DoCmd.TransferText(TransferType:=acExportDelim, _
                            SpecificationName:="FTP Spec", _
                            TableName:="qryFTP", _
                            FileName:=strTemp, _
                            HasFieldNames:=False)
    If Exist(strFile) Then Call KillFile(strFile)
    Name strTemp As strFile
    Call ClearTable(strTable:="tblCMD", strWhere:=conClearCMD)
    strMsg = Replace("Testing FTP script.%L%L" _
                   & "This process should be very quick (<10 seconds).%L" _
                   , "%L", vbNewLine)
    Call MsgBox(Prompt:=strMsg, _
                Buttons:=vbInformation Or vbOKOnly, _
                TITLE:=CurrentProject.NAME)
    strTemp = MultiReplace("CMD.EXE /T:1E /C " _
                         & "Start 'FTP Test' /D '%F' /MAX /WAIT /B " _
                         & "FTP.EXE -s:SCRIPT.FTP&&PAUSE" _
                         , "'", """" _
                         , "%F", strFo)
    Call ShellWait(strCommand:=strTemp, intWinStyle:=vbMaximizedFocus)
    If Exist(strFile) Then Call KillFile(strFile)
    Call SwitchMode(strType:=strMode)
    TestFTP = True
    Exit Function
 
ErrorHandler:
    If Exist(strFile) Then Call KillFile(strFile)
    If Exist(strTemp) Then Call KillFile(strTemp)
    Call ClearTable(strTable:="tblCMD", strWhere:=conClearCMD)
    strMsg = MultiReplace("Error (%N) :%L%D%L%L" & _
                          "Unable to complete FTP Test", _
                          "%N", Err, _
                          "%D", Err.DESCRIPTION, _
                          "%L", vbNewLine)
    Call MsgBox(Prompt:=strMsg, Buttons:=vbCritical Or vbOKOnly, TITLE:=strOr)
    Call SwitchMode(strType:=strMode)
End Function

'SetStrings() prepares the global string variables strA, strB, strF & strO.
Public Sub SetStrings()
    If strAc = "" Then
        With CurrentProject
            strAc = BareFolder(SysCmd(acSysCmdAccessDir)) & "\MSAccess.Exe"
            strOr = .NAME
            strFo = Left(.Path, 1)
            If strFo >= "A" And strFo <= "Z" And strFo <> Left(CurDir, 1) Then _
                Call ChDrive(Drive:=strFo)
            strFo = BareFolder(.Path)
            If BareFolder(CurDir) <> strFo Then Call ChDir(Path:=strFo)
        End With
    End If
End Sub

Conclusion.

There are few limits to what you can control from within Access. I include an attachment with this that can be extracted to any folder and run. The attachment uses an Add-Ins toolbar to give access to the items to test. It may immediately upgrade you the first time you run. Don't worry. The upgraded version is exactly the same except with a higher version number stored internally. Use the Add-In toolbar to test the FTP process. This should work for you exactly as it is until the end of June 2016. If you want to use it after that point simply provide an FTP server and credentials in the modRelease module. See the picture in the Overall Concept section above for the lines of code to change.