473,837 Members | 1,531 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Control External Processes using Scripts - CMD

32,584 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. CMD.EXE, in particular though, gives control of a whole script file's worth of almost any commands. 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).

That's what I'll be explaining in this article. Another, in the same subject area, is Control External Processes using Scripts - FTP.

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 this 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 this case, and 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. 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 Upgrade CMD script is %Ac, which is used for the full name of the Access executable. This is used differently for the FTP 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 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 from the table.

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  Cmd1                            Template  Type  Order  Cmd1
  2.   TRUE     U      10   @ECHO OFF                         FALSE    U      10   @ECHO OFF
  3.   TRUE     U      20   IF NOT EXIST "%Fo\%Ba" GOTO RENAMEBACKUP
  4.                                                          FALSE    U      20   IF NOT EXIST "D:\Scratch\Access\ControlExternalProcesses\ContEx_Last.Accdb" GOTO RENAMEBACKUP
  5.   TRUE     U      30   ATTRIB -R "%Fo\%Ba" >NUL          FALSE    U      30   ATTRIB -R "D:\Scratch\Access\ControlExternalProcesses\ContEx_Last.Accdb" >NUL
  6.   TRUE     U      40   DEL "%Fo\%Ba" >NUL                FALSE    U      40   DEL "D:\Scratch\Access\ControlExternalProcesses\ContEx_Last.Accdb" >NUL
  8.   TRUE     U      60   :RENAMEBACKUP                     FALSE    U      60   :RENAMEBACKUP
  9.   TRUE     U      70   ECHO Attempting to rename '%Fo\%Or' to '%Ba'
  10.                                                          FALSE    U      70   ECHO Attempting to rename 'D:\Scratch\Access\ControlExternalProcesses\ContEx.Accdb' to 'ContEx_Last.Accdb'
  11.   TRUE     U      80   ECHO Needs "%Fo\%Or" to be closed before continuing...
  12.                                                          FALSE    U      80   ECHO Needs "D:\Scratch\Access\ControlExternalProcesses\ContEx.Accdb" to be closed before continuing...
  13.   TRUE     U      90   :START                            FALSE    U      90   :START
  14.   TRUE     U     100  REN "%Fo\%Or" "%Ba" >NUL           FALSE    U     100   REN "D:\Scratch\Access\ControlExternalProcesses\ContEx.Accdb" "ContEx_Last.Accdb" >NUL
  15.   TRUE     U     110  IF ERRORLEVEL 1 GOTO START         FALSE    U     110   IF ERRORLEVEL 1 GOTO START
  16.   TRUE     U     120  ATTRIB +R "%Fo\%Ba" >NUL           FALSE    U     120   ATTRIB +R "D:\Scratch\Access\ControlExternalProcesses\ContEx_Last.Accdb" >NUL
  17.   TRUE     U     130  ECHO Attempting to copy '%Ne' to '%Fo\%Or'
  18.                                                          FALSE    U     130   ECHO Attempting to copy 'D:\Scratch\Access\ControlExternalProcesses\Release\ContEx01.00.00.Accdb' to 'D:\Scratch\Access\ControlExternalProcesses\ContEx.Accdb'
  19.   TRUE     U     140  COPY /B /V /Y "%Ne" "%Fo\%Or" >NUL FALSE    U     140   COPY /B /V /Y "D:\Scratch\Access\ControlExternalProcesses\Release\ContEx01.00.00.Accdb" "D:\Scratch\Access\ControlExternalProcesses\ContEx.Accdb" >NUL
  21.   TRUE     U     160  ATTRIB -R -S -H "%Fo\%Or" >NUL     FALSE    U     160   ATTRIB -R -S -H "D:\Scratch\Access\ControlExternalProcesses\ContEx.Accdb" >NUL
  23.   TRUE     U     180  ECHO Restarting Access with the updated file.
  24.                                                          FALSE    U     180   ECHO Restarting Access with the updated file.
  25.   TRUE     U     190  START "Dummy Title" /D"%Fo" "%Ac"
  26. [Cmd2]                "%Fo\%Or" ;%BE
  27.                                                          FALSE    U     190   START "Dummy Title" /D"D:\Scratch\Access\ControlExternalProcesses" "C:\Program Files (x86)\Microsoft Office\Office14\MSAccess.Exe"
  28. [Cmd2]                                                                        "D:\Scratch\Access\ControlExternalProcesses\ContEx.Accdb" ;
  29.   TRUE     U     200  GOTO CMDEND                        FALSE    U     200   GOTO CMDEND
  30.   TRUE     U     210  :BADBACKUP                         FALSE    U     210   :BADBACKUP
  31.   TRUE     U     220  ECHO Unable to delete existing backup file '%Fo\%Ba'.
  32.                                                          FALSE    U     220   ECHO Unable to delete existing backup file 'D:\Scratch\Access\ControlExternalProcesses\ContEx_Last.Accdb'.
  33.   TRUE     U     230  GOTO CMDERROR                      FALSE    U     230   GOTO CMDERROR
  34.   TRUE     U     240  :BADCOPY                           FALSE    U     240   :BADCOPY
  35.   TRUE     U     250  ECHO Unable to copy '%Ne' to '%Fo\%Or' successfully.
  36.                                                          FALSE    U     250   ECHO Unable to copy 'D:\Scratch\Access\ControlExternalProcesses\Release\ContEx01.00.00.Accdb' to 'D:\Scratch\Access\ControlExternalProcesses\ContEx.Accdb' successfully.
  37.   TRUE     U     260  :CMDERROR                          FALSE    U     260   :CMDERROR
  38.   TRUE     U     270  ECHO Please refer this problem to Support.
  39.                                                          FALSE    U     270   ECHO Please refer this problem to Support.
  40.   TRUE     U     280  PAUSE                              FALSE    U     280   PAUSE
  41.   TRUE     U     290  :CMDEND                            FALSE    U     290   :CMDEND
  42.   TRUE     U     300  DEL "%Fo\UPGRADE.CMD" >NUL         FALSE    U     300   DEL "D:\Scratch\Access\ControlExternalProcesses\UPGRADE.CMD" >NUL
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.

Data Explanation.

This is a CMD script and it will be called by the Access code shortly (but with a small delay) before the Access database then closes. The overall purpose is to replace the Access database file with a new version, then invoke the new file just like the old one. As a safety measure, the current file is saved with the same name except for "_Last" being appended. If anything were to go wrong then this file can be re-enabled simply by renaming it over the new one.

Many of the lines are less fundamental to the whole process and are simply there to provide as reliable a process as possible. I'll limit my explanation to the fundamental lines.
  1. Lines #20 through #50 ensure any existing _Last file is deleted before the existing one is renamed.
  2. Lines #60 through #110 try to rename the current file to the _Last file.
    This can only work once the current file has closed successfully. This process loops through trying until it succeeds.
    Another variation of this concept is simply to rename a file to itself in a loop. Once it succeeds you know the file is free.
  3. Lines #120 through #170 copy the new version of the file in place of the current one.
  4. Lines #180 through #190 start up Access again directly into the current database.
    This uses [Cmd2] because it also passes a parameter to the database telling it which back-end file to link to. This is not generally required but this illustrates how it can be done if it is.
  5. Lines #210 through #280 handle error conditions.
  6. Lines #290 & #300 finish off the process by deleting the script file and ending.


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 #26 through #37. This is where the parameters are replaced. To see the effect of this put a breakpoint on line #60 and print the value of strSQL at that point in the code.

I've included the code for SetStrings() below. It's basically used for getting the folder that we're running the project from as well as other required values.
  1. Lines to #19 set things up and clear away any files.
  2. Line #20 determines whether or not there is an upgrade file that can be used to replace the current one.
    This logic isn't relevant to the concept. It's simply an example of one way of using it.
  3. Line #22 sets up the variable strVersion with the version number from the file that was found.
  4. Line #23 finds the last dot (.) of the current filename. IE. Where the extension is.
  5. Lines #24 through #25 set up strBa which is one of the replaceable parameters used in the data (%Ba).
  6. Lines #26 through #37 set up the value of strCmd which is what is used to replace all the replaceable parameters with values in the SQL.
  7. Lines #38 through #59 set up the SQL using strCmd twice. Once for [Cmd1], and again for [Cmd2].
  8. Line #60 sets up dbVar to point to the current database.
  9. Line #62 saves the value of the Auto Compact property. This is important in case the process fails and needs to be restored.
  10. Lines #62 through #63 use the SQL in strSQL to add the live data to the table, but only after clearing up any that may have been left from before.
    This data will be used to create the CMD file.
  11. Lines #64 & #75 set intErr to indicate to the ErrorHandler what needs to be done to recover in case the process crashes.
  12. Lines #65 through #71 first delete any file if it's there, then export the new data to it using DoCmd.TransferT ext.
    NB. The file must be a .TXT file for this to work.
  13. Lines #72 through #73 first clear the actual CMD file if it exists, then rename the .TXT file to .CMD.
  14. Line #74 deletes the new data in the table now it's been exported and is no longer required.
  15. Line #76 sets the Auto Compact property off to avoid any delay closing the current database.
  16. Lines #76 through #83 prompt the operator and warn them of what's about to happen.
  17. Line #84 invokes the CMD file we just created.
    This will run and loop around until the database is successfully closed, when it will do the copying and renaming of the files before finally invoking the new database in Access and deleting itself (The CMD file).

Expand|Select|Wrap|Line Numbers
  1. 'UpgradeProject() prepares to upgrade from strN and returns true if all ok.
  2. '  If all ok then calling code needs to close and quit the whole application
  3. '  in order for the process to continue.
  4. '12/09/2012 strBE used to ensure the upgraded FE uses the current BE or one
  5. '           passed in the shortcut.
  6. Public Function UpgradeProject() As Boolean
  7.     Dim intX As Integer, intErr As Integer, intCompact As Integer
  8.     Dim strNe As String, strVersion As String, strSQL As String
  9.     Dim strMsg As String, strMode As String, strCmd As String
  10.     Dim dbVar As DAO.Database
  12.     On Error GoTo Error_UpgradeProject
  13.     strMode = SwitchMode(strType:="Process")
  14.     Call SetStrings
  15.     'Before we go any further, and regardless of whether or not an upgrade is
  16.     '  even required, let's clear away any existing copy of UPGRADE.CMD.
  17.     '  It's checked again immediately prior to being created.
  18.     If Exist(strFo & "\UPGRADECMD.Txt") Then _
  19.         Call KillFile(strFo & "\UPGRADECMD.Txt")
  20.     strNe = GetUpgradeFile()
  21.     If strNe = "" Then Exit Function
  22.     strVersion = FormatVersion(strNe, "Display")
  23.     intX = InStrRev(StringCheck:=strOr, StringMatch:=".")
  24.     strBa = MultiReplace("%S_Last%F", "%S", Left(strOr, intX - 1), _
  25.                                       "%F", Mid(strOr, intX))
  26.     strCmd = "Replace(Nz([~C],''),'%Ac','%sAc')"
  27.     strCmd = Replace("Replace(%C,'%Ba','%sBa')", "%C", strCmd)
  28.     strCmd = Replace("Replace(%C,'%BE','%sBE')", "%C", strCmd)
  29.     strCmd = Replace("Replace(%C,'%Fo','%sFo')", "%C", strCmd)
  30.     strCmd = Replace("Replace(%C,'%Ne','%sNe')", "%C", strCmd)
  31.     strCmd = Replace("Replace(%C,'%Or','%sOr')", "%C", strCmd)
  32.     strCmd = MultiReplace(strCmd, "%sAc", strAc, _
  33.                                   "%sBa", strBa, _
  34.                                   "%sBE", "", _
  35.                                   "%sFo", strFo, _
  36.                                   "%sNe", strNe, _
  37.                                   "%sOr", strOr)
  38.     strSQL = "INSERT INTO [tblCMD]%L" _
  39.            & "       ([Template]%L" _
  40.            & "      , [Type]%L" _
  41.            & "      , [Order]%L" _
  42.            & "      , [Cmd1]%L" _
  43.            & "      , [Cmd2])%L" _
  44.            & "SELECT  [Template]%L" _
  45.            & "      , [Type]%L" _
  46.            & "      , [Order]%L" _
  47.            & "      , [C1] AS [Cmd1]%L" _
  48.            & "      , IIf([C2]='',Null,[C2]) AS [Cmd2]%L" _
  49.            & "FROM    (SELECT False AS [Template]%L" _
  50.            & "              , [Type]%L" _
  51.            & "              , [Order]%L" _
  52.            & "              , %C1 AS [C1]%L" _
  53.            & "              , %C2 AS [C2]%L" _
  54.            & "         FROM   [tblCMD]%L" _
  55.            & "         WHERE  ([Template])%L" _
  56.            & "           AND  ([Type]='U')) AS [qC]"
  57.     strSQL = MultiReplace(strSQL, "%C1", Replace(strCmd, "~C", "Cmd1") _
  58.                                 , "%C2", Replace(strCmd, "~C", "Cmd2") _
  59.                                 , "%L", vbNewLine)
  60.     Set dbVar = CurrentDb()
  61.     intCompact = dbVar.Properties("Auto Compact")
  62.     Call ClearTable(strTable:="tblCMD", strWhere:="(NOT [Template])")
  63.     Call dbVar.Execute(Query:=strSQL, Options:=dbFailOnError)
  64.     intErr = &H1
  65.     If Exist(strFo & "\UPGRADECMD.Txt") Then _
  66.         Call KillFile(strFo & "\UPGRADECMD.Txt")
  67.     Call DoCmd.TransferText(TransferType:=acExportDelim, _
  68.                             SpecificationName:="Upgrade Spec", _
  69.                             TableName:="qryUpgrade", _
  70.                             FileName:=strFo & "\UPGRADECMD.Txt", _
  71.                             HasFieldNames:=False)
  72.     If Exist(strFo & "\UPGRADE.CMD") Then Call KillFile(strFo & "\UPGRADE.CMD")
  73.     Name strFo & "\UPGRADECMD.Txt" As strFo & "\UPGRADE.CMD"
  74.     Call ClearTable(strTable:="tblCMD", strWhere:="(NOT [Template])")
  75.     intErr = &H0
  76.     dbVar.Properties("Auto Compact") = 0
  77.     strMsg = MultiReplace("Upgrading from '%N'.%L%L" _
  78.                         & "This process should be very quick (<1 minute).%L" _
  79.                         , "%N", strNe _
  80.                         , "%L", vbNewLine)
  81.     Call MsgBox(Prompt:=strMsg, _
  82.                 Buttons:=vbInformation Or vbOKOnly, _
  83.                 TITLE:=CurrentProject.NAME)
  84.     Call Shell(PathName:=strFo & "\UPGRADE.CMD", WindowStyle:=vbNormalFocus)
  85.     Call SwitchMode(strType:=strMode)
  86.     UpgradeProject = True
  87.     Exit Function
  89. Error_UpgradeProject:
  90.     If Not dbVar Is Nothing Then dbVar.Properties("Auto Compact") = intCompact
  91.     If Exist(strFo & "\UPGRADE.CMD") Then Call KillFile(strFo & "\UPGRADE.CMD")
  92.     If Exist(strFo & "\UPGRADECMD.Txt") Then _
  93.         Call KillFile(strFo & "\UPGRADECMD.Txt")
  94.     If (intErr And &H1) Then _
  95.         Call ClearTable(strTable:="tblCMD", strWhere:="(NOT [Template])")
  96.     strMsg = MultiReplace("Error (%N) :%L%D%L%L" & _
  97.                           "Unable to complete upgrade process", _
  98.                           "%N", Err, _
  99.                           "%D", Err.DESCRIPTION, _
  100.                           "%L", vbNewLine)
  101.     Call MsgBox(Prompt:=strMsg, Buttons:=vbCritical Or vbOKOnly, TITLE:=strOr)
  102.     Call SwitchMode(strType:=strMode)
  103. 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 Upgrade process manually.

Apr 4 '16 #1
2 8715
220 New Member
Years later and I found this article helpful to me.

Thank you NeoPa.
Jul 22 '20 #2
32,584 Recognized Expert Moderator MVP
Always a pleasure.

Having it here to point to means it can be useful for years to come :-)
Jul 22 '20 #3

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

Similar topics

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: NeoPa | last post by:
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...
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: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
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: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
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: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
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 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.