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 :
Table=[tblCMD]
Expand|Select|Wrap|Line Numbers
- Field Name Type PK (Compound)
- Template Boolean #1
- Type String(1) #2
- LineNo Long #3
- Cmd String(255)
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.
Table=[tblCMD]
Expand|Select|Wrap|Line Numbers
- Template Type Order Cmd1 Template Type Order Cmd1
- TRUE U 10 @ECHO OFF FALSE U 10 @ECHO OFF
- TRUE U 20 IF NOT EXIST "%Fo\%Ba" GOTO RENAMEBACKUP
- FALSE U 20 IF NOT EXIST "D:\Scratch\Access\ControlExternalProcesses\ContEx_Last.Accdb" GOTO RENAMEBACKUP
- TRUE U 30 ATTRIB -R "%Fo\%Ba" >NUL FALSE U 30 ATTRIB -R "D:\Scratch\Access\ControlExternalProcesses\ContEx_Last.Accdb" >NUL
- TRUE U 40 DEL "%Fo\%Ba" >NUL FALSE U 40 DEL "D:\Scratch\Access\ControlExternalProcesses\ContEx_Last.Accdb" >NUL
- TRUE U 50 IF ERRORLEVEL 1 GOTO BADBACKUP FALSE U 50 IF ERRORLEVEL 1 GOTO BADBACKUP
- TRUE U 60 :RENAMEBACKUP FALSE U 60 :RENAMEBACKUP
- TRUE U 70 ECHO Attempting to rename '%Fo\%Or' to '%Ba'
- FALSE U 70 ECHO Attempting to rename 'D:\Scratch\Access\ControlExternalProcesses\ContEx.Accdb' to 'ContEx_Last.Accdb'
- TRUE U 80 ECHO Needs "%Fo\%Or" to be closed before continuing...
- FALSE U 80 ECHO Needs "D:\Scratch\Access\ControlExternalProcesses\ContEx.Accdb" to be closed before continuing...
- TRUE U 90 :START FALSE U 90 :START
- TRUE U 100 REN "%Fo\%Or" "%Ba" >NUL FALSE U 100 REN "D:\Scratch\Access\ControlExternalProcesses\ContEx.Accdb" "ContEx_Last.Accdb" >NUL
- TRUE U 110 IF ERRORLEVEL 1 GOTO START FALSE U 110 IF ERRORLEVEL 1 GOTO START
- TRUE U 120 ATTRIB +R "%Fo\%Ba" >NUL FALSE U 120 ATTRIB +R "D:\Scratch\Access\ControlExternalProcesses\ContEx_Last.Accdb" >NUL
- TRUE U 130 ECHO Attempting to copy '%Ne' to '%Fo\%Or'
- FALSE U 130 ECHO Attempting to copy 'D:\Scratch\Access\ControlExternalProcesses\Release\ContEx01.00.00.Accdb' to 'D:\Scratch\Access\ControlExternalProcesses\ContEx.Accdb'
- 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
- TRUE U 150 IF ERRORLEVEL 1 GOTO BADCOPY FALSE U 150 IF ERRORLEVEL 1 GOTO BADCOPY
- TRUE U 160 ATTRIB -R -S -H "%Fo\%Or" >NUL FALSE U 160 ATTRIB -R -S -H "D:\Scratch\Access\ControlExternalProcesses\ContEx.Accdb" >NUL
- TRUE U 170 IF ERRORLEVEL 1 GOTO BADCOPY FALSE U 170 IF ERRORLEVEL 1 GOTO BADCOPY
- TRUE U 180 ECHO Restarting Access with the updated file.
- FALSE U 180 ECHO Restarting Access with the updated file.
- TRUE U 190 START "Dummy Title" /D"%Fo" "%Ac"
- [Cmd2] "%Fo\%Or" ;%BE
- FALSE U 190 START "Dummy Title" /D"D:\Scratch\Access\ControlExternalProcesses" "C:\Program Files (x86)\Microsoft Office\Office14\MSAccess.Exe"
- [Cmd2] "D:\Scratch\Access\ControlExternalProcesses\ContEx.Accdb" ;
- TRUE U 200 GOTO CMDEND FALSE U 200 GOTO CMDEND
- TRUE U 210 :BADBACKUP FALSE U 210 :BADBACKUP
- TRUE U 220 ECHO Unable to delete existing backup file '%Fo\%Ba'.
- FALSE U 220 ECHO Unable to delete existing backup file 'D:\Scratch\Access\ControlExternalProcesses\ContEx_Last.Accdb'.
- TRUE U 230 GOTO CMDERROR FALSE U 230 GOTO CMDERROR
- TRUE U 240 :BADCOPY FALSE U 240 :BADCOPY
- TRUE U 250 ECHO Unable to copy '%Ne' to '%Fo\%Or' successfully.
- FALSE U 250 ECHO Unable to copy 'D:\Scratch\Access\ControlExternalProcesses\Release\ContEx01.00.00.Accdb' to 'D:\Scratch\Access\ControlExternalProcesses\ContEx.Accdb' successfully.
- TRUE U 260 :CMDERROR FALSE U 260 :CMDERROR
- TRUE U 270 ECHO Please refer this problem to Support.
- FALSE U 270 ECHO Please refer this problem to Support.
- TRUE U 280 PAUSE FALSE U 280 PAUSE
- TRUE U 290 :CMDEND FALSE U 290 :CMDEND
- TRUE U 300 DEL "%Fo\UPGRADE.CMD" >NUL FALSE U 300 DEL "D:\Scratch\Access\ControlExternalProcesses\UPGRADE.CMD" >NUL
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.
- Lines #20 through #50 ensure any existing _Last file is deleted before the existing one is renamed.
- 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. - Lines #120 through #170 copy the new version of the file in place of the current one.
- 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. - Lines #210 through #280 handle error conditions.
- Lines #290 & #300 finish off the process by deleting the script file and ending.
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 #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.- Lines to #19 set things up and clear away any files.
- 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. - Line #22 sets up the variable strVersion with the version number from the file that was found.
- Line #23 finds the last dot (.) of the current filename. IE. Where the extension is.
- Lines #24 through #25 set up strBa which is one of the replaceable parameters used in the data (%Ba).
- 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.
- Lines #38 through #59 set up the SQL using strCmd twice. Once for [Cmd1], and again for [Cmd2].
- Line #60 sets up dbVar to point to the current database.
- Line #62 saves the value of the Auto Compact property. This is important in case the process fails and needs to be restored.
- 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. - Lines #64 & #75 set intErr to indicate to the ErrorHandler what needs to be done to recover in case the process crashes.
- 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. - Lines #72 through #73 first clear the actual CMD file if it exists, then rename the .TXT file to .CMD.
- Line #74 deletes the new data in the table now it's been exported and is no longer required.
- Line #76 sets the Auto Compact property off to avoid any delay closing the current database.
- Lines #76 through #83 prompt the operator and warn them of what's about to happen.
- 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
- 'UpgradeProject() prepares to upgrade from strN and returns true if all ok.
- ' If all ok then calling code needs to close and quit the whole application
- ' in order for the process to continue.
- '12/09/2012 strBE used to ensure the upgraded FE uses the current BE or one
- ' passed in the shortcut.
- Public Function UpgradeProject() As Boolean
- Dim intX As Integer, intErr As Integer, intCompact As Integer
- Dim strNe As String, strVersion As String, strSQL As String
- Dim strMsg As String, strMode As String, strCmd As String
- Dim dbVar As DAO.Database
- On Error GoTo Error_UpgradeProject
- strMode = SwitchMode(strType:="Process")
- Call SetStrings
- 'Before we go any further, and regardless of whether or not an upgrade is
- ' even required, let's clear away any existing copy of UPGRADE.CMD.
- ' It's checked again immediately prior to being created.
- If Exist(strFo & "\UPGRADECMD.Txt") Then _
- Call KillFile(strFo & "\UPGRADECMD.Txt")
- strNe = GetUpgradeFile()
- If strNe = "" Then Exit Function
- strVersion = FormatVersion(strNe, "Display")
- intX = InStrRev(StringCheck:=strOr, StringMatch:=".")
- strBa = MultiReplace("%S_Last%F", "%S", Left(strOr, intX - 1), _
- "%F", Mid(strOr, intX))
- strCmd = "Replace(Nz([~C],''),'%Ac','%sAc')"
- strCmd = Replace("Replace(%C,'%Ba','%sBa')", "%C", strCmd)
- strCmd = Replace("Replace(%C,'%BE','%sBE')", "%C", strCmd)
- strCmd = Replace("Replace(%C,'%Fo','%sFo')", "%C", strCmd)
- strCmd = Replace("Replace(%C,'%Ne','%sNe')", "%C", strCmd)
- strCmd = Replace("Replace(%C,'%Or','%sOr')", "%C", strCmd)
- strCmd = MultiReplace(strCmd, "%sAc", strAc, _
- "%sBa", strBa, _
- "%sBE", "", _
- "%sFo", strFo, _
- "%sNe", strNe, _
- "%sOr", strOr)
- strSQL = "INSERT INTO [tblCMD]%L" _
- & " ([Template]%L" _
- & " , [Type]%L" _
- & " , [Order]%L" _
- & " , [Cmd1]%L" _
- & " , [Cmd2])%L" _
- & "SELECT [Template]%L" _
- & " , [Type]%L" _
- & " , [Order]%L" _
- & " , [C1] AS [Cmd1]%L" _
- & " , IIf([C2]='',Null,[C2]) AS [Cmd2]%L" _
- & "FROM (SELECT False AS [Template]%L" _
- & " , [Type]%L" _
- & " , [Order]%L" _
- & " , %C1 AS [C1]%L" _
- & " , %C2 AS [C2]%L" _
- & " FROM [tblCMD]%L" _
- & " WHERE ([Template])%L" _
- & " AND ([Type]='U')) AS [qC]"
- strSQL = MultiReplace(strSQL, "%C1", Replace(strCmd, "~C", "Cmd1") _
- , "%C2", Replace(strCmd, "~C", "Cmd2") _
- , "%L", vbNewLine)
- Set dbVar = CurrentDb()
- intCompact = dbVar.Properties("Auto Compact")
- Call ClearTable(strTable:="tblCMD", strWhere:="(NOT [Template])")
- Call dbVar.Execute(Query:=strSQL, Options:=dbFailOnError)
- intErr = &H1
- If Exist(strFo & "\UPGRADECMD.Txt") Then _
- Call KillFile(strFo & "\UPGRADECMD.Txt")
- Call DoCmd.TransferText(TransferType:=acExportDelim, _
- SpecificationName:="Upgrade Spec", _
- TableName:="qryUpgrade", _
- FileName:=strFo & "\UPGRADECMD.Txt", _
- HasFieldNames:=False)
- If Exist(strFo & "\UPGRADE.CMD") Then Call KillFile(strFo & "\UPGRADE.CMD")
- Name strFo & "\UPGRADECMD.Txt" As strFo & "\UPGRADE.CMD"
- Call ClearTable(strTable:="tblCMD", strWhere:="(NOT [Template])")
- intErr = &H0
- dbVar.Properties("Auto Compact") = 0
- strMsg = MultiReplace("Upgrading from '%N'.%L%L" _
- & "This process should be very quick (<1 minute).%L" _
- , "%N", strNe _
- , "%L", vbNewLine)
- Call MsgBox(Prompt:=strMsg, _
- Buttons:=vbInformation Or vbOKOnly, _
- TITLE:=CurrentProject.NAME)
- Call Shell(PathName:=strFo & "\UPGRADE.CMD", WindowStyle:=vbNormalFocus)
- Call SwitchMode(strType:=strMode)
- UpgradeProject = True
- Exit Function
- Error_UpgradeProject:
- If Not dbVar Is Nothing Then dbVar.Properties("Auto Compact") = intCompact
- If Exist(strFo & "\UPGRADE.CMD") Then Call KillFile(strFo & "\UPGRADE.CMD")
- If Exist(strFo & "\UPGRADECMD.Txt") Then _
- Call KillFile(strFo & "\UPGRADECMD.Txt")
- If (intErr And &H1) Then _
- Call ClearTable(strTable:="tblCMD", strWhere:="(NOT [Template])")
- strMsg = MultiReplace("Error (%N) :%L%D%L%L" & _
- "Unable to complete upgrade process", _
- "%N", Err, _
- "%D", Err.DESCRIPTION, _
- "%L", vbNewLine)
- Call MsgBox(Prompt:=strMsg, Buttons:=vbCritical Or vbOKOnly, TITLE:=strOr)
- Call SwitchMode(strType:=strMode)
- End Function
Expand|Select|Wrap|Line Numbers
- '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 Upgrade process manually.