422,023 Members | 1,004 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Control External Processes using Scripts - CMD

Expert Mod 15k+
P: 30,549

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.TransferText.
    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
Share this Article
Share on Google+