473,718 Members | 1,955 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Control External Processes using Scripts - FTP

32,569 Recognized Expert Moderator MVP

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 :

Expand|Select|Wrap|Line Numbers
  1. Field Name  Type        PK (Compound)
  2. Template    Boolean     #1
  3. Type        String(1)   #2
  4. LineNo      Long        #3
  5. 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.

Expand|Select|Wrap|Line Numbers
  1. Template  Type  Order  Cmd                            Template  Type  Order  Cmd
  2.   TRUE      F     10   Open %FS                         FALSE     F     10   Open FTP.AccessConsultantUK.co.uk
  3.   TRUE      F     20   %Ac                              FALSE     F     20   ACUKdemo
  4.   TRUE      F     30   %PW                              FALSE     F     30   PublicPW
  5.   TRUE      F     40   binary                           FALSE     F     40   binary
  6.   TRUE      F     50   mkdir test                       FALSE     F     50   mkdir test
  7.   TRUE      F     60   cd test                          FALSE     F     60   cd test
  8.   TRUE      F     70   send ACLogo.JPG sent.file        FALSE     F     70   send ACLogo.JPG sent.file
  9.   TRUE      F     80   dir                              FALSE     F     80   dir
  10.   TRUE      F     90   rename sent.file renamed.file    FALSE     F     90   rename sent.file renamed.file
  11.   TRUE      F    100   dir                              FALSE     F     100  dir
  12.   TRUE      F    110   get renamed.file BackAgain.JPG   FALSE     F     110  get renamed.file BackAgain.JPG
  13.   TRUE      F    120   delete renamed.file              FALSE     F     120  delete renamed.file
  14.   TRUE      F    130   rmdir test                       FALSE     F     130  rmdir test
  15.   TRUE      F    140   dir                              FALSE     F     140  dir
  16.   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.


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.TransferT ext() 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.

Expand|Select|Wrap|Line Numbers
  1. 'TestFTP() creates and runs the script file to test FTP.
  2. Public Function TestFTP() As Boolean
  3.     Dim strAccount As String, strFTPServer As String, strFile As String
  4.     Dim strTemp As String, strSQL As String, strMsg As String, strCmd As String
  5.     Dim dbVar As DAO.Database
  7.     On Error GoTo ErrorHandler
  8.     strMode = SwitchMode(strType:="Process")
  9.     Call SetStrings
  10.     strFile = strFo & "\SCRIPT.FTP"
  11.     strTemp = strFo & "\SCRIPTFTP.Txt"
  12.     'Before we go any further, and regardless of whether or not we run the
  13.     '  script, let's clear away any existing copy of SCRIPTFTP.Txt.
  14.     '  It's checked again immediately prior to being created.
  15.     If Exist(strTemp) Then Call KillFile(strTemp)
  16.     strCmd = "Replace(Nz([~C],''),'%FS','%sFS')"
  17.     strCmd = Replace("Replace(%C,'%Ac','%sAc')", "%C", strCmd)
  18.     strCmd = Replace("Replace(%C,'%PW','%sPW')", "%C", strCmd)
  19.     strCmd = MultiReplace(strCmd, "%sFS", conFTPServer _
  20.                                 , "%sAc", conFTPAccount _
  21.                                 , "%sPW", Scramble(conFTPPW))
  22.     strSQL = "INSERT INTO [tblCMD]%L" _
  23.            & "      ( [Template]%L" _
  24.            & "      , [Type]%L" _
  25.            & "      , [Order]%L" _
  26.            & "      , [Cmd1]%L" _
  27.            & "      , [Cmd2])%L" _
  28.            & "SELECT  False AS [Template]%L" _
  29.            & "      , [Type]%L" _
  30.            & "      , [Order]%L" _
  31.            & "      , [C1] AS [Cmd1]%L" _
  32.            & "      , Null AS [Cmd2]%L" _
  33.            & "FROM    (SELECT [Type]%L" _
  34.            & "              , [Order]%L" _
  35.            & "              , %C1 AS [C1]%L" _
  36.            & "         FROM   [tblCMD]%L" _
  37.            & "         WHERE  ([Template])%L" _
  38.            & "           AND  ([Type]='F')) AS [qC]"
  39.     strSQL = MultiReplace(strSQL, "%C1", Replace(strCmd, "~C", "Cmd1") _
  40.                                 , "%L", vbNewLine)
  41.     Set dbVar = CurrentDb()
  42.     Call ClearTable(strTable:="tblCMD", strWhere:=conClearCMD)
  43.     Call dbVar.Execute(Query:=strSQL, Options:=dbFailOnError)
  44.     If Exist(strTemp) Then Call KillFile(strTemp)
  45.     Call DoCmd.TransferText(TransferType:=acExportDelim, _
  46.                             SpecificationName:="FTP Spec", _
  47.                             TableName:="qryFTP", _
  48.                             FileName:=strTemp, _
  49.                             HasFieldNames:=False)
  50.     If Exist(strFile) Then Call KillFile(strFile)
  51.     Name strTemp As strFile
  52.     Call ClearTable(strTable:="tblCMD", strWhere:=conClearCMD)
  53.     strMsg = Replace("Testing FTP script.%L%L" _
  54.                    & "This process should be very quick (<10 seconds).%L" _
  55.                    , "%L", vbNewLine)
  56.     Call MsgBox(Prompt:=strMsg, _
  57.                 Buttons:=vbInformation Or vbOKOnly, _
  58.                 TITLE:=CurrentProject.NAME)
  59.     strTemp = MultiReplace("CMD.EXE /T:1E /C " _
  60.                          & "Start 'FTP Test' /D '%F' /MAX /WAIT /B " _
  61.                          & "FTP.EXE -s:SCRIPT.FTP&&PAUSE" _
  62.                          , "'", """" _
  63.                          , "%F", strFo)
  64.     Call ShellWait(strCommand:=strTemp, intWinStyle:=vbMaximizedFocus)
  65.     If Exist(strFile) Then Call KillFile(strFile)
  66.     Call SwitchMode(strType:=strMode)
  67.     TestFTP = True
  68.     Exit Function
  70. ErrorHandler:
  71.     If Exist(strFile) Then Call KillFile(strFile)
  72.     If Exist(strTemp) Then Call KillFile(strTemp)
  73.     Call ClearTable(strTable:="tblCMD", strWhere:=conClearCMD)
  74.     strMsg = MultiReplace("Error (%N) :%L%D%L%L" & _
  75.                           "Unable to complete FTP Test", _
  76.                           "%N", Err, _
  77.                           "%D", Err.DESCRIPTION, _
  78.                           "%L", vbNewLine)
  79.     Call MsgBox(Prompt:=strMsg, Buttons:=vbCritical Or vbOKOnly, TITLE:=strOr)
  80.     Call SwitchMode(strType:=strMode)
  81. End Function
Expand|Select|Wrap|Line Numbers
  1. 'SetStrings() prepares the global string variables strA, strB, strF & strO.
  2. Public Sub SetStrings()
  3.     If strAc = "" Then
  4.         With CurrentProject
  5.             strAc = BareFolder(SysCmd(acSysCmdAccessDir)) & "\MSAccess.Exe"
  6.             strOr = .NAME
  7.             strFo = Left(.Path, 1)
  8.             If strFo >= "A" And strFo <= "Z" And strFo <> Left(CurDir, 1) Then _
  9.                 Call ChDrive(Drive:=strFo)
  10.             strFo = BareFolder(.Path)
  11.             If BareFolder(CurDir) <> strFo Then Call ChDir(Path:=strFo)
  12.         End With
  13.     End If
  14. End Sub


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.

Attached Images
File Type: jpg CEPReplacements.jpg (72.7 KB, 7706 views)
File Type: jpg CEPToolbar.JPG (12.9 KB, 6571 views)
Attached Files
File Type: zip ControlExternalProcesses.ZIP (1.60 MB, 544 views)
Mar 28 '16 #1
0 7406

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Filipe Bonjour | last post by:
Hi, I'm used to Unix, and shell scripting for doing a variety of admin tasks. Recently, my company started using Windows as well, and I'm looking into the possibility of rewriting some of our scripts (Korn shell) for Windows, since we will need much the same functionality. I'd like to try it in Perl, for possible portability back to Unix (so I woul donly have a set of scripts
by: AKR | last post by:
I have to create control panel applet using C# . I have read in the net that it is not possible using C# . How can i create control panel applet using C# .
by: Henke | last post by:
I get a unresolved external error when linking this code. Does anybody know why? ..h-file namespace TestDll { __gc class Class1 { public: Class1();
by: Jimmy V | last post by:
Hi all, I am a VB programmer who is moving out of the shadows and starting to code in C#. I would like to know how to determine a control's type using a swtich statement. In VB i would do something like this: Private Sub TestControl(ByRef PassedControl as Control) Select Case True Case TypeOf PassedControl Is TextBox
by: arunasunil | last post by:
Hi, Is there a way to track external processes launched by python on the Mac? I am using subprocess module to launch the process. Thanks Sunil
by: nekha | last post by:
how to send alert message to another member now iam in online using scripts
by: shashikantbokaro1976 | last post by:
Hi all, How to create a control panel applets using c#.
by: vingomail | last post by:
How can I clear the history of a browser page by using scripts or programming
by: kamakshi k | last post by:
is it possible to repeat one control many times using for loop? like, for(i=0;i<5;i++) { textbox1.text; } anybody know plz help me
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.