<

Deploy and update a Microsoft Access application with one click

Published on
10,168 Points
1,068 Views
1 Endorsement
Last Modified:
Deploying a Microsoft Access application in a normal Windows environment is not difficult but takes a few steps. The method and script provided here will - literally - turn the process into a one-click process for the user, even in a Citrix environment.

The goal

There are many ways to deploy a Microsoft Access application. Many of these require several steps for the user to carry out, and some don't take updates into account.


The method here, however, requires only one click from the user to install and run the application. Further, it will allow for fully automatic updates of the application at the developer's will - only one simple file copy of the new version is all it takes, and it can be done while some or all users have launched the application.


The user needs only direct access to a shortcut file. This can be located in a network folder, retrieved from a URL, or attached an e-mail. When double-clicked the first time, it will install the application and a Desktop shortcut. The next time, a fresh copy of the application will be pulled from the distribution folder - updating the current copy, or replacing a perhaps corrupted or bloated frontend file.


How it is done

It is quite simple to implement:


  • a double-click on a shortcut opens a script
  • the script runs and takes care of the rest:
    • copies the application file to a local folder
    • sets the Registry entries for trusting this local folder
    • copies the shortcut to the user's Desktop folder
    • launches the application


When the user closes the application, the shortcut is present on the Desktop. To launch the application again, the user will double-click the shortcut, and the process described above is repeated.


The basic structure for the distribution folder and files is like:



As shown, in the main distribution folder is only the shortcut and a subfolder found.

The subfolder (here: Files) holds three files only: 


  1. The application file - of type accdb, accde, or accdr
  2. An icon file (optional, though recommended) for the shortcut
  3. The script


An extended structure can include two or more sets of shortcuts and subfolders, for example:


  • Distribution\Production
  • Distribution\Test


The shortcut to distribute to the users is identical to the one listed at the top in the picture above. Have in mind, that it is a shortcut to the script file, not to Microsoft Access or the application file.


To update the application, just update the application file in the subfolder. As the users sign off and later relaunch the application, the application file will be copied to the user's local folder overwriting the previous copy, and then the new version is launched. It can't be easier.


Of course, a downgrade can also be performed; replace the application file with a previous version, that's all.


The requirements

To deploy and run your Access application as described, you will need:


  • to have Microsoft Access installed either as a full install or as a runtime
  • a local folder path where the user has been granted full rights                            
  • to set some security settings for a trusted location in the Registry                          
  • a script to copy your application file and create a Desktop shortcut
  • a shortcut to call the script


Microsoft Access (runtime)

How this is installed and which version to choose is of no importance for the process and the script. It was created for Microsoft Access 2010, should work for 2013 and 2019, and has been tested with 2016/365.


How to install and configure either of these - as a full install or as a runtime - is beyond the scope of this article.


The local folder

The best folder path to use is LocalAppData. To see where it lives, type %localappdata%  in Windows Explorer:


                         

 and press Enter. It will resolve to something like:


C:\Users\NameOfUserProfile\AppData\Local


Here you will typically create a subfolder and a subfolder of this, like: 


\OrganisationName\ApplicationName


to host the local copy of the application file. 

Using the distribution folder and file example names listed above, the resulting local structure will - apart from the shortcut file in the Desktop folder - contain one file only:



Registry settings

These are specific for each version of Microsoft Access, so pay attention. 


A single line in the script controls this. By default it is set to fit Microsoft Access 2016 and Microsoft Access 365:


' Environment specific constants.
    '
    ' Expected version of Microsoft Access - the returned value of property:
    '   ? Access.Application.Version
    Const AccessVersion = "16.0"

The Registry key holding the settings will (for version 16.0) be:


HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Access\Security\


It will hold an entry for a trusted location assigned the local folder above and will prevent a warning message from Microsoft Access to be displayed when launching the application.


The Script

The script is written in VBScript and may look quite comprehensive. However, it is not convoluted, and the in-line comments explain each and every step. The main tasks, it performs, are these:


  • Verify/create the local folders
  • Copy the application file to the local folder and the shortcut to the Desktop
  • Write Registry entries for Microsoft Access security
  • Launch the application


The full listing is:


Option Explicit

' Launch script for a Microsoft Access application.
' Version 2.0.2
' 2019-01-15
' Cactus Data. Gustav Brock

' ---------------------------------------------------------------------------------
' This script file must be placed in a distribution folder, like:
'   F:\Distribution\AppName
'
' That folder must have subfolder(s) for the app type(s).
' - for one app type only, for example:
'	F:\Distribution\AppName\Files
' - for, say, three app types:
'	F:\Distribution\AppName\Operations
'	F:\Distribution\AppName\Test
'	F:\Distribution\AppName\Development
'
' Specify the next constants for a resulting install path of:
'	%LocalAppData%\OrgSubfolderName\AppSubfolderName\AppTypeSubfolderName
' - for example resulting in:
'	C:\Users\UserProfileName\AppData\Local\Organisation\AppName\Operations
' ---------------------------------------------------------------------------------

' ---------------------------------------------------------------------------------
' Environment specific constants.
    '
    ' Expected version of Microsoft Access - the returned value of property:
    '   ? Access.Application.Version
    Const AccessVersion = "16.0"
' ---------------------------------------------------------------------------------

' ---------------------------------------------------------------------------------
' Application specific constants.
    '
    ' Source filename.
    Const AppBaseName = "DMadresser"
    ' Extension name. Uncomment ONE extension name only.
    'Const AppExtensionName = "accdb"
    'Const AppExtensionName = "accde"
    Const AppExtensionName = "accdr"
    ' Optional suffix.
    Const AppNoColourSuffix = "NC"
    
    ' Local install folder names. Will be (sub)subfolders of %LocalAppData%.
    Const OrgSubfolderName = "DM"
    Const AppSubfolderName = "DM Administration"
    
    ' Shortcut name(s). Uncomment ONE folder name ONLY:
    Const ShortcutBaseName = "DM Adresser"
    'Const ShortcutBaseName = "DM Adresser Test"
    
    ' Title of the application when running. For TaskKill in subfunction KillTask.
    Const AppWindowTitle = "DM ADRESSER"
' ---------------------------------------------------------------------------------

' ---------------------------------------------------------------------------------
' Installation specific constants.
    
    ' Distribution folder names. Uncomment ONE folder name ONLY:
    Const AppTypeSubfolderName = "Files"
    'Const AppTypeSubfolderName = "Development"
    'Const AppTypeSubfolderName = "Operations"
    'Const AppTypeSubfolderName = "Test"

    ' Indicate if the script is for the normal version (0) or a no-colour version (1):
    Const NoColour = 0

    ' Force a close of an open application even if blocked by a modal message box.
    Const ForceClose = True
' ---------------------------------------------------------------------------------


' ---------------------------------------------------------------------------------
' Script.

    ' Windows folder constants.
    Const DESKTOP = &H10
    Const LOCALAPPDATA = &H1C
    ' Extension of a shortcut.
    Const ShortcutExtensionName = "lnk"

    ' Objects.
    Dim FileSystemObject
    Dim AppShell
    Dim DesktopFolder
    Dim LocalAppDataFolder
    Dim LocalFolder
    Dim RemoteFolder

    ' Variables.
    Dim LocalFolderName
    Dim RemoteFolderName
    Dim DesktopFolderName
    Dim LocalAppDataFolderName
    Dim LocalAppDataOrgFolderName
    Dim LocalAppDataOrgAppFolderName
    Dim AppName
    Dim AppExtension
    Dim AppSuffix
    Dim ShortcutName
    Dim AppLocalPath
    Dim AppRemotePath
    Dim ShortcutExtension
    Dim ShortcutLocalPath
    Dim ShortcutRemotePath
    Dim RegPath
    Dim RegKey
    Dim RegValue
    Dim Value


    ' Create the Shell object and the File System Object.
    Set FileSystemObject = CreateObject("Scripting.FileSystemObject")
    Set AppShell = CreateObject("Shell.Application")

    ' Build distribution folder name.
    RemoteFolderName = FileSystemObject.GetParentFolderName(WScript.ScriptFullName)

    ' Build filenames.
    If NoColour = 1 Then
        AppSuffix = AppNoColourSuffix
    Else
        AppSuffix = ""
    End If
    AppExtension = "." & AppExtensionName
    AppName = AppBaseName & AppSuffix & AppExtension
    ShortcutExtension = "." & ShortcutExtensionName
    ShortcutName = ShortcutBaseName & AppSuffix & ShortcutExtension

    ' Enable in-line error handling.
    On Error Resume Next

    ' Find user's Desktop and AppData\Local folder.
    Set DesktopFolder = AppShell.Namespace(DESKTOP)
    DesktopFolderName = DesktopFolder.Self.Path
    Set LocalAppDataFolder = AppShell.Namespace(LOCALAPPDATA)
    LocalAppDataFolderName = LocalAppDataFolder.Self.Path

    ' Uncomment to debug.
    'WScript.Echo "Desktop: " & DesktopFolderName & vbCrLf & "LocalAppData: " & LocalAppDataFolderName

    ' Dynamic parameters.
    LocalAppDataOrgFolderName = FileSystemObject.BuildPath(LocalAppDataFolderName, OrgSubfolderName)
    LocalAppDataOrgAppFolderName = FileSystemObject.BuildPath(LocalAppDataOrgFolderName, AppSubfolderName)
    LocalFolderName = FileSystemObject.BuildPath(LocalAppDataOrgAppFolderName, AppTypeSubfolderName)
    AppLocalPath = FileSystemObject.BuildPath(LocalFolderName, AppName)
    ShortcutLocalPath = FileSystemObject.BuildPath(DesktopFolderName, ShortcutName)

    ' Permanent parameters.
    AppRemotePath = FileSystemObject.BuildPath(RemoteFolderName, AppName)
    ShortcutRemotePath = FileSystemObject.BuildPath(FileSystemObject.BuildPath(RemoteFolderName, ".."), ShortcutName)

    ' Verify/create the local folders.
    If Not FileSystemObject.FolderExists(RemoteFolderName) Then
        Call ErrorHandler("No access to " & RemoteFolderName & ".")
    Else
        Set RemoteFolder = FileSystemObject.GetFolder(RemoteFolderName)
        ' If the local folder does not exist, create the folder.
        If Not FileSystemObject.FolderExists(LocalFolderName) Then
            If Not FileSystemObject.FolderExists(LocalAppDataOrgFolderName) Then
                Set LocalFolder = FileSystemObject.CreateFolder(LocalAppDataOrgFolderName)
                If Not Err.Number = vbEmpty Then
                    Call ErrorHandler("Folder " & LocalAppDataOrgFolderName & " could not be created.")
                End If
            End If
            If Not FileSystemObject.FolderExists(LocalAppDataOrgAppFolderName) Then
                Set LocalFolder = FileSystemObject.CreateFolder(LocalAppDataOrgAppFolderName)
                If Not Err.Number = vbEmpty Then
                    Call ErrorHandler("Folder " & LocalAppDataOrgAppFolderName & " could not be created.")
                End If
            End If
            If Not FileSystemObject.FolderExists(LocalFolderName) Then
                Set LocalFolder = FileSystemObject.CreateFolder(LocalFolderName)
                If Not Err.Number = vbEmpty Then
                    Call ErrorHandler("Folder " & LocalFolderName & " could not be created.")
                End If
            End If
        End If
        Set LocalFolder = FileSystemObject.GetFolder(LocalFolderName)
    End If

    ' Copy the distribution file to the local folder and the shortcut to the Desktop.
    If Not FileSystemObject.FileExists(AppRemotePath) Then
        Call ErrorHandler("The application file:" & vbCrLf & AppRemotePath & vbCrLf & "could not be found.")
    Else
        ' First, close a running application - using the setting of constant ForceClose.
        Call KillTask(AppWindowTitle)
        ' Wait while TaskKill is running to close the instance of the application.
        Call AwaitProcess("taskkill.exe")

        ' Copy app to local folder.
        If FileSystemObject.FileExists(AppLocalPath) Then
            FileSystemObject.DeleteFile(AppLocalPath)
            If Not Err.Number = 0 Then
                If IsProcess("MSACCESS.EXE") Then
                    ' The application may be blocked for closing by a modal message box.
                    MsgBox "Cannot update or reinstall the application while it is running.", vbCritical + vbOkOnly, AppWindowTitle
                    WScript.Quit        
                Else
                    Call ErrorHandler("The application file:" & vbCrLf & AppName & vbCrLf & "can not be refreshed/updated. It may be in use.")
                End If
            End If
        End If
        If FileSystemObject.FileExists(AppLocalPath) Then
            Call ErrorHandler("The local application file:" & vbCrLf & AppLocalPath & vbCrLf & "could not be replaced.")
        Else
            FileSystemObject.CopyFile AppRemotePath, AppLocalPath
            If Not Err.Number = vbEmpty Then
                Call ErrorHandler("Application could not be copied to " & LocalFolderName & ".")
            End If
        End If

        ' Uncomment to debug.
        'WScript.Echo "Shortcut remote: " & ShortcutRemotePath & vbCrLf & "Shortcut local: " & ShortcutLocalPath

        ' Copy shortcut.
        FileSystemObject.CopyFile ShortcutRemotePath, ShortcutLocalPath
        If Not Err.Number = vbEmpty Then
            Call ErrorHandler("Shortcut could not be copied to your Desktop.")
        End If
    End If

    ' Write Registry entries for Microsoft Access security.
    RegKey = "HKEY_CURRENT_USER\Software\Microsoft\Office\" & AccessVersion & "\Access\Security\"
    RegValue = "VBAWarnings"
    RegPath = RegKey & RegValue
    Value = 1
    Call WriteRegistry(RegPath, Value,"REG_DWORD")

    RegKey = RegKey & "Trusted Locations\LocationLocalAppData\"
    RegValue = "AllowSubfolders"
    RegPath = RegKey & RegValue
    Value = 1
    Call WriteRegistry(RegPath, Value, "REG_DWORD")

    RegValue = "Date"
    RegPath = RegKey & RegValue
    Value = Now
    Value = FormatDateTime(Value, vbShortDate) & " " & FormatDateTime(Value, vbShortTime)
    Call WriteRegistry(RegPath, Value, "REG_SZ")

    RegValue = "Description"
    RegPath = RegKey & RegValue
    Value = "Local AppData"
    Call WriteRegistry(RegPath, Value, "REG_SZ")

    RegValue = "Path"
    RegPath = RegKey & RegValue
    Value = LocalAppDataFolderName & "\"
    Call WriteRegistry(RegPath, Value, "REG_SZ")

    ' Launch the application.
    If FileSystemObject.FileExists(AppLocalPath) Then
        Call RunApp(AppLocalPath)
    Else
        Call ErrorHandler("The local application file:" & vbCrLf & AppLocalPath & vbCrLf & "could not be found.")
    End If

    Set RemoteFolder = Nothing
    Set LocalFolder = Nothing
    Set LocalAppDataFolder = Nothing
    Set DesktopFolder = Nothing
    Set AppShell = Nothing
    Set FileSystemObject = Nothing

    ' Exit.
    WScript.Quit

' Exit script.
' ---------------------------------------------------------------------------------


' ---------------------------------------------------------------------------------
' Supporting (sub)functions.

Sub RunApp(ByVal Filename)

    Const vbNormalFocus = 1
    Const WaitOnReturn = False

    Dim Shell
    Dim Command
    Dim WindowStyle

    ' Open as default foreground application.
    WindowStyle = vbNormalFocus

    Set Shell = CreateObject("WScript.Shell")
    Command = """" & Filename & """"
    Shell.Run Command, WindowStyle, WaitOnReturn
    
    Set Shell = Nothing

End Sub


Sub KillTask(ByVal WindowTitle)

    Const vbMinimizedNoFocus = 7
    Const WaitOnReturn = False
    Const ForcedCloseOn = "/F"
    Const ForcedCloseOff = ""

    Dim Shell
    Dim Command
    Dim WindowStyle
    Dim CloseStyle

    ' Run silently.
    WindowStyle = vbMinimizedNoFocus

    Set Shell = CreateObject("WScript.Shell")
    If ForceClose = True Then
        CloseStyle = ForcedCloseOn
    Else
        CloseStyle = ForcedCloseOff
    End If
    Command = "TaskKill.exe /FI ""WINDOWTITLE eq " & WindowTitle & """ " & CloseStyle
    Shell.Run Command, WindowStyle, WaitOnReturn

    Set Shell = Nothing

End Sub


Sub AwaitProcess(ByVal Process)

    Dim Service
    Dim Query
    Dim Processes
    Dim Count

    Set Service = GetObject("winmgmts:root\cimv2")
    Query = "select * from win32_process where name = '" & Process & "'"

    Do
        Set Processes = Service.Execquery(Query)
        Count = Processes.Count
        If Count > 0 Then
            WScript.Sleep 300
        End If
    Loop Until Count = 0

    Set Processes = Nothing
    Set Service = Nothing

End Sub

Function IsProcess(ByVal Process)

    Dim Service
    Dim Query
    Dim Processes
    Dim Result

    Set Service = GetObject("winmgmts:root\cimv2")
    Query = "select * from win32_process where name = '" & Process & "'"

    Set Processes = Service.Execquery(Query)
    If Processes.Count > 0 Then
        Result = True
    Else
        Result = False
    End If

    Set Processes = Nothing
    Set Service = Nothing

    IsProcess = Result

End Function


Sub WriteRegistry(ByVal RegPath, ByVal Value, ByVal RegType)
    ' RegType should be:
    '   "REG_SZ" for a string
    '   "REG_DWORD" for an integer
    '   "REG_BINARY" for a binary or boolean
    '   "REG_EXPAND_SZ" for an expandable string

    Dim Shell

    Set Shell = CreateObject("WScript.Shell")

    Call Shell.RegWrite(RegPath, Value, RegType)

    Set Shell = Nothing

End Sub


Sub ErrorHandler(Byval Message)

    Set RemoteFolder = Nothing
    Set LocalFolder = Nothing
    Set LocalAppDataFolder = Nothing
    Set DesktopFolder = Nothing
    Set AppShell = Nothing
    Set FileSystemObject = Nothing

    MsgBox Message, vbExclamation + vbOkOnly, ShortcutBaseName
    WScript.Quit

End Sub


' End script.
' ---------------------------------------------------------------------------------

All settings for a specific environment and the behaviour of the script is controlled by constants in the top of the script. They are all carefully commented, so it should be easy to make it run as to your requirements.


Should you wish to modify the script beyond simple adjustments of the constants or prompts, I can strongly recommend using Visual Studio Code for this, as it applies a nice emphasising of keywords as well as control of indention as seen from this snippet:



Potential blocking

Of course, the user must be allowed to run a VBScript for this setup to work. Also, Windows 10 has a special option for blocking changes to the Desktop from "unknown apps". If that is the case, the user will be presented for a localised slide-in message like this:



and the script will fail as it cannot run as intended.


The shortcut

This is very basic, and could appear like this example:



Though not mandatory, I will strongly recommend attaching an icon file (as shown).


Citrix

This method for deployment will also work in a Citrix environment. That was covered in the previous article:


Deploy and update a Microsoft Access application in a Citrix environment


and the comments about Citrix still applies. The script presented here is, in fact, a brushed-up version of the original script. A major difference between the previous script and the script listed here is, that the previous script deployed two copies of the application file, while this script - as is the case for far most scenarios - deploys one copy only.


Conclusion

By using the method described and the script attached and presented here, you will have a very simple method to deploy and update a Microsoft Access application that is completely transparent for the user. Also, it will require little if any maintenance.


Download

The script is attached as a text file: Setup DMadresser.txt

After download, rename it to have an extension of vbs, for example: Setup YourApplication.vbs


I hope you found this article useful. You are encouraged to ask questions, report any bugs or make any other comments about it below.


Note: If you need further "Support" about this topic, please consider using the Ask a Question feature of Experts Exchange. I monitor questions asked and would be pleased to provide any additional support required in questions asked in this manner, along with other EE experts.


Please do not forget to press the "Thumb's Up" button if you think this article was helpful and valuable for EE members.


1
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free