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.
It is quite simple to implement:
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:
An extended structure can include two or more sets of shortcuts and subfolders, for example:
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.
To deploy and run your Access application as described, you will need:
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 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:
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 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:
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:
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.
This is very basic, and could appear like this example:
Though not mandatory, I will strongly recommend attaching an icon file (as shown).
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.
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.
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.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (2)
Commented:
I saw this article come up in my email as a Page Editor and immediately knew that this was something for which I had been looking. I am a relative newbie when it comes to Visual Basic but have been trying nonetheless. I must be doing something wrong, when I download and edit your script (an excellent one) it keeps looking to F:\Documents. I thought I changed everything, but I guess not - could you point me in the right direction?
Never mind - I figured it out and it now seems soooooooooooooooooo obvious!
Author
Commented:Everything to change/modify in the script is above these lines:
Open in new window
Here, I have an example in this folder:
P:\Cactus Administration\Distributio
having the folder and file:
Files <folder>
Cactus TimeSag <the shortcut file>
In folder Files I have these files:
CactusTimesag.vbs <the script>
Timesag.ico
Timesag4.accdr
These are the parameters in the script:
Open in new window
The shortcut destination is:
"P:\Cactus Administration\Distributio
Start in:
"P:\Cactus Administration\Distributio
Icon file:
"P:\Cactus Administration\Distributio
Note, that the distribution folder can be "anything" without modifying the script. Only the subfolder matters (here: Files).
This should guide you. No programming, only administration.