Deploying Access with VBScript

He is fully retired, but he may decide to drop in...
 
NauticalGent said:
Rumor has it that VB Script is going away - that isn't to say this will stop working immediately, just that VB Script is no longer being supported. With that in mind, I have, with a huge amount of help from ChatGPT, converted this to PowerShell. If you are interested, I can share that.
Click to expand...
I'd be very interested in that please NG.
 
I decided to post it here just in case anyone else was interested. The comments should make everything clear, but if not, don't hesitate to ask. I used this as a demo for one of the Access User Groups so I left all the names intact, simply replace the paths and names and you should be good to go.

Code: 
<#
.SYNOPSIS
    This script updates a local Access database from a server and launches Microsoft Access with the updated database.

.DESCRIPTION
    The script performs the following actions:
    - Checks if the required local directory exists and creates it if it does not.
    - Copies the specified files from a server to the local machine.
    - Attempts to launch Microsoft Access with the updated database file.
    - Logs actions to a log file and displays status messages via GUI pop-ups.

.VERSION
    1.0

.AUTHOR
    John Clark (with extensive help from ChatGPT!)
    Design concept from Bill Mosca

.DATE
    May 9, 2024

.NOTES
    Ensure that the paths and permissions are correctly set before running this script.
    Requires PowerShell 5.0 or later and appropriate permissions to access network resources and local paths.

.EXAMPLE
    PS> .\UpdateAndLaunchAccess.ps1

#>

# Define constants
$cTXTFILE = "AUG_Demo_DB_feVer20240509b.pdf"
$cFE = "AUG_Demo.accdb"
$cICON = "aug_demo_icon.ico"
$cSVRPATH = "\\ls210d202\AccessUserGroups"
$cLOCPATH = "C:\AUG_Demo"
$cScriptPath = "\\ls210d202\AccessUserGroups\LaunchAUG_Demo.ps1"
$cLogPath = "\\ls210d202\AccessUserGroups\AUG_Demo_LogFile.log"

# Function to log messages
function Write-Log {
    param ([string]$Message)
    Add-content -Path $cLogPath -Value "$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss'): $Message"
}

# Function to create a desktop shortcut to this script if it doesn't exist
function CreateShortcut {
    $DesktopPath = [System.Environment]::GetFolderPath('Desktop')
    $ShortcutPath = Join-Path -Path $DesktopPath -ChildPath "Update and Launch AUG Demo.lnk"
    if (-not (Test-Path $ShortcutPath)) {
        $WshShell = New-Object -ComObject WScript.Shell
        $Shortcut = $WshShell.CreateShortcut($ShortcutPath)
        $Shortcut.TargetPath = "powershell.exe"
        $Shortcut.Arguments = "-ExecutionPolicy Bypass -File `"$cScriptPath`""
        $Shortcut.IconLocation = "$cLOCPATH\$cICON"
        $Shortcut.Description = "Launch and Update AUG Demo Database"
        $Shortcut.Save()
        Write-Log "Shortcut created on Desktop."
    }
}

# Function to copy database files from the server to the local machine
function UpdateDBFiles {
    try {
        Write-Log "Checking if local path exists."
        if (-not (Test-Path $cLOCPATH)) {
            New-Item -Path $cLOCPATH -ItemType Directory | Out-Null
            Write-Log "Created local directory at $cLOCPATH."
        }
        Copy-Item -Path "$cSVRPATH\$cTXTFILE" -Destination $cLOCPATH -Force
        Copy-Item -Path "$cSVRPATH\$cFE" -Destination $cLOCPATH -Force
        Copy-Item -Path "$cSVRPATH\$cICON" -Destination $cLOCPATH -Force
        Write-Log "Files updated successfully from $cSVRPATH to $cLOCPATH."
    } catch {
        Write-Log "Failed to update files: $_"
        [System.Windows.MessageBox]::Show("Failed to update files: $_", "Error", [System.Windows.MessageBoxButton]::OK, [System.Windows.MessageBoxImage]::Error)
        exit
    }
}

# Function to launch the Access application with the updated database
function LaunchAccess {
    try {
        $msAccessPath = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\MSACCESS.EXE\').Path
        if (-not $msAccessPath) {
            Write-Log "MS Access is not installed."
            [System.Windows.MessageBox]::Show("MS Access is not installed.", "Error", [System.Windows.MessageBoxButton]::OK, [System.Windows.MessageBoxImage]::Error)
            exit
        }
        Start-Process -FilePath "$msAccessPath\msaccess.exe" -ArgumentList "`"$cLOCPATH\$cFE`""
        Write-Log "MS Access launched with database: $cLOCPATH\$cFE."
    } catch {
        Write-Log "Failed to launch MS Access: $_"
        [System.Windows.MessageBox]::Show("Failed to launch MS Access: $_", "Error", [System.Windows.MessageBoxButton]::OK, [System.Windows.MessageBoxImage]::Error)
        exit
    }
}

# Main function that runs update, launch, and shortcut check operations
function Main {
    CreateShortcut
    UpdateDBFiles
    LaunchAccess
    [System.Windows.MessageBox]::Show("Operation completed successfully!", "Success", [System.Windows.MessageBoxButton]::OK, [System.Windows.MessageBoxImage]::Information)
}

# Adding necessary .NET assembly for MessageBox
Add-Type -AssemblyName System.Windows.Forms

# Execute the main function
Main
 
NauticalGent said:
I decided to post it here just in case anyone else was interested. The comments should make everything clear, but if not, don't hesitate to ask. I used this as a demo for one of the Access User Groups so I left all the names intact, simply replace the paths and names and you should be good to go.

Code: 
<#
.SYNOPSIS
    This script updates a local Access database from a server and launches Microsoft Access with the updated database.

.DESCRIPTION
    The script performs the following actions:
    - Checks if the required local directory exists and creates it if it does not.
    - Copies the specified files from a server to the local machine.
    - Attempts to launch Microsoft Access with the updated database file.
    - Logs actions to a log file and displays status messages via GUI pop-ups.

.VERSION
    1.0

.AUTHOR
    John Clark (with extensive help from ChatGPT!)
    Design concept from Bill Mosca

.DATE
    May 9, 2024

.NOTES
    Ensure that the paths and permissions are correctly set before running this script.
    Requires PowerShell 5.0 or later and appropriate permissions to access network resources and local paths.

.EXAMPLE
    PS> .\UpdateAndLaunchAccess.ps1

#>

# Define constants
$cTXTFILE = "AUG_Demo_DB_feVer20240509b.pdf"
$cFE = "AUG_Demo.accdb"
$cICON = "aug_demo_icon.ico"
$cSVRPATH = "\\ls210d202\AccessUserGroups"
$cLOCPATH = "C:\AUG_Demo"
$cScriptPath = "\\ls210d202\AccessUserGroups\LaunchAUG_Demo.ps1"
$cLogPath = "\\ls210d202\AccessUserGroups\AUG_Demo_LogFile.log"

# Function to log messages
function Write-Log {
    param ([string]$Message)
    Add-content -Path $cLogPath -Value "$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss'): $Message"
}

# Function to create a desktop shortcut to this script if it doesn't exist
function CreateShortcut {
    $DesktopPath = [System.Environment]::GetFolderPath('Desktop')
    $ShortcutPath = Join-Path -Path $DesktopPath -ChildPath "Update and Launch AUG Demo.lnk"
    if (-not (Test-Path $ShortcutPath)) {
        $WshShell = New-Object -ComObject WScript.Shell
        $Shortcut = $WshShell.CreateShortcut($ShortcutPath)
        $Shortcut.TargetPath = "powershell.exe"
        $Shortcut.Arguments = "-ExecutionPolicy Bypass -File `"$cScriptPath`""
        $Shortcut.IconLocation = "$cLOCPATH\$cICON"
        $Shortcut.Description = "Launch and Update AUG Demo Database"
        $Shortcut.Save()
        Write-Log "Shortcut created on Desktop."
    }
}

# Function to copy database files from the server to the local machine
function UpdateDBFiles {
    try {
        Write-Log "Checking if local path exists."
        if (-not (Test-Path $cLOCPATH)) {
            New-Item -Path $cLOCPATH -ItemType Directory | Out-Null
            Write-Log "Created local directory at $cLOCPATH."
        }
        Copy-Item -Path "$cSVRPATH\$cTXTFILE" -Destination $cLOCPATH -Force
        Copy-Item -Path "$cSVRPATH\$cFE" -Destination $cLOCPATH -Force
        Copy-Item -Path "$cSVRPATH\$cICON" -Destination $cLOCPATH -Force
        Write-Log "Files updated successfully from $cSVRPATH to $cLOCPATH."
    } catch {
        Write-Log "Failed to update files: $_"
        [System.Windows.MessageBox]::Show("Failed to update files: $_", "Error", [System.Windows.MessageBoxButton]::OK, [System.Windows.MessageBoxImage]::Error)
        exit
    }
}

# Function to launch the Access application with the updated database
function LaunchAccess {
    try {
        $msAccessPath = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\MSACCESS.EXE\').Path
        if (-not $msAccessPath) {
            Write-Log "MS Access is not installed."
            [System.Windows.MessageBox]::Show("MS Access is not installed.", "Error", [System.Windows.MessageBoxButton]::OK, [System.Windows.MessageBoxImage]::Error)
            exit
        }
        Start-Process -FilePath "$msAccessPath\msaccess.exe" -ArgumentList "`"$cLOCPATH\$cFE`""
        Write-Log "MS Access launched with database: $cLOCPATH\$cFE."
    } catch {
        Write-Log "Failed to launch MS Access: $_"
        [System.Windows.MessageBox]::Show("Failed to launch MS Access: $_", "Error", [System.Windows.MessageBoxButton]::OK, [System.Windows.MessageBoxImage]::Error)
        exit
    }
}

# Main function that runs update, launch, and shortcut check operations
function Main {
    CreateShortcut
    UpdateDBFiles
    LaunchAccess
    [System.Windows.MessageBox]::Show("Operation completed successfully!", "Success", [System.Windows.MessageBoxButton]::OK, [System.Windows.MessageBoxImage]::Information)
}

# Adding necessary .NET assembly for MessageBox
Add-Type -AssemblyName System.Windows.Forms

# Execute the main function
Main
Click to expand...
Excellent! You Sir are a star :)
 
NauticalGent said:
You are quite welcome, let me know how your fare...
Click to expand...
Works a treat. Apart from the winforms GUI. For some reason (could be our security settings GP) that assembly just won't load. Or at least loads without error and then won't run... I'm using winforms elsewhere not in PS so I assume the .NET bits and bobs are present on my machine.
I'll put up some screen shots in due course.
Need coffee.

Cancel above. It works fine this morning both as admin and basic user. Deffo need more coffee.
 
Last edited:
NickCoe(UK) said:
Works a treat. Apart from the winforms GUI. For some reason (could be our security settings GP) that assembly just won't load. Or at least loads without error and then won't run... I'm using winforms elsewhere not in PS so I assume the .NET bits and bobs are present on my machine.
I'll put up some screen shots in due course.
Need coffee.

Cancel above. It works fine this morning both as admin and basic user. Deffo need more coffee.
Click to expand...
I was wrong. Works in ISE.
Screenform doesn't work if called in terminal. Still succesfully copies the files, creates shortcut etc.
I'm tempted to remark out the winforms bits. Most of our users are IT engineers and tend to be a bit hasty and knowall. The completion form will probably be seen as an irritant, damn them :)

1732868238145.png
 
You must log in or register to reply here.

Similar threads

C
Merge form data with Word template, then auto publish to PDF
Replies
0
Views
1,475
chadbrewyet
C
speakers_86
Create DB and make it hidden
Replies
12
Views
1,869
vbaInet
V

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom