Powershell Commands through VBA

[mhorner]

Hi everybody,

I created and manage an access database at work, and recently we've upgraded to Windows 10 / Office 365. I have 1 hurdle I am struggling to resolve and was hoping for guidance if anybody has time and would be so kind:

The database in question here is a split database. I have a batch file that creates a backup copy of both the front and back end. I built a command button on a form to call this batch. Perfect.

Now that we've been pushed to Windows 10 / Office 365, employees no longer have administrator privileges AND batch files are blocked entirely by company policy. I was told to rewrite my batch as a power shell script.

I learned powershell and rewrote my batch. Excellent. Long story short though, because we don't have admin priveledges anymore, every machine defaults to blocking scripts until you change the execution policy to bypass.

So, I can officially call powershell.exe, but the script won't open. What I, for the life of me, cannot figure out -- is: How do you pass MULTIPLE powershell commands through VBA? I'd like to set the execution policy every time the shell is called right before the script opens. I need to send the following commands during the VBA call line:

1. set-executionpolicy -scope currentuser
2. a
3. ./backup.ps1

My code looks like this to call the shell:
'x = Shell("POWERSHELL.exe -noexit CD " & "'FILE PATH HERE'", vbNormalFocus)

This lets me pass the CD [Insert file path here] command through to powershell and change the directory. But I need to take this a few steps further. After changing the directory, I need to set the execution policy then run the script. Any idea what the syntax is to pass 3 separate powershell commands through VBA to a call to a single instance of powershell?

[theDBguy]

Hi. I haven't learned how to code in PowerShell but is not possible to call another script from one script? This was possible with batch files, wasn't it. It's the same idea as calling another VBA function from within a different VBA function. I'll try to do a search now, but I was just wondering if this is not possible at all. If it is, then maybe you could try it out.

[theDBguy]

By the way, did you say you were using the script to perform a backup copy of your db? If so, are you not able to use VBA to do the same thing? Just curious...

[theDBguy]

Hi. See if this previous discussion helps...

[mhorner]

Originally Posted by theDBguy

Hi. I haven't learned how to code in PowerShell but is not possible to call another script from one script? This was possible with batch files, wasn't it. It's the same idea as calling another VBA function from within a different VBA function. I'll try to do a search now, but I was just wondering if this is not possible at all. If it is, then maybe you could try it out.

I'm not sure this would be possible because I cannot call a script because scripts are blocked by company policy until I change the execution policy in powershell upon calling each instance of powershell.

Originally Posted by theDBguy

By the way, did you say you were using the script to perform a backup copy of your db? If so, are you not able to use VBA to do the same thing? Just curious...

Correct, I am using a powershell script now. I previously used a batch and just called the batch. I suppose it would be possible in VBA, there are probably 10,000 ways to achieve creating a backup routine. Maybe I can look into how to accomplish this with VBA. I figured I already have the powershell script written and working, just need to figure out how to pass a few powershell commands through before calling the script.

Originally Posted by theDBguy

Hi. See if this previous discussion helps...

Interesting, this may be the answer I was looking for? Looks like this gentlemen sends multiple PS commands to a single PS process by using a combination of the Invoke command and the + sign. It may take me a few days to find the time to try this out, but certainly a starting point. Thank you for sharing, let me tinker with this possible solution and follow up here with the turnout.

Thanks again for the quick replies! Much appreciated.

[theDBguy]

Hi. You're welcome. I hope you get it to work. And if you do, please let us know and consider posting the solution as well. In case you want to also check out VBA, see if you can modify this approach to suit your needs. Good luck!

[mhorner]

Originally Posted by theDBguy

Hi. You're welcome. I hope you get it to work. And if you do, please let us know and consider posting the solution as well. In case you want to also check out VBA, see if you can modify this approach to suit your needs. Good luck!

Good morning DBGuy, thanks for your help. I did get this working, but abandoned the powershell script and tackled both the front and back end backup routines directly through VBA. I ended up using the link you sent previously as a foundation and added some folder detection and creation to match the folder structure I already have established.

The way my backup folders structured are:
G:\Database\Backup\[Year]\[Month]\[Day]\Database Back End ([Year]-[Month]-[Day]-[Time])

Every time the backup routine is run, this looks into the G:\Database\Backup folder to determine if a folder already exists for the year of today's date. If not, it will create one. If yes, it will determine if a folder exists inside the year folder whose name is the month of today's date. If not, it will create one. If yes, it will determine if a folder exists inside the month folder whose name is the day of today's date. If not, it will create one. It then proceeds to backup both the front and back end databases into said folder, then prompts the user that the backup was successfull (and states the name and file path of the backups).

*Updated filepath to UNC filepath.
*Added second source variable to front end file.
*Updated variable names to reflect front and back end.
*Cleaned the confirmation message box to user.

Private Sub BackUpAndCompactBE_Click()
On Error GoTo errHandler
    
    Dim oFSO As Object
    Dim strDestinationBE As String
    Dim strDestinationFE As String
    Dim strSourceBE As String
    Dim strSourceFE As String
    
    'Get the source of your back end
    strSourceBE = Split(Split(CurrentDb.TableDefs("tblTABLENAME").Connect, _
            "Database=")(1), ";")(0)
    Debug.Print "Back End Source: " & strSourceBE
        
    'Get the source of your front end
    strSourceFE = "\\FILEPATH\FILEPATH\DatabaseFE.accdb"
    Debug.Print "Front End Source: " & strSourceFE
        
    'Determine your destination
    strYear = Year(Now)
    strMonth = Month(Now)
    strDay = Day(Now)
    
    Dim Path As String
    Dim Folder As String
    Dim Answer As VbMsgBoxResult
    Path = "\\FILEPATH\FILEPATH\Database\Backup\" & strYear
    Folder = Dir(Path, vbDirectory)
        If Folder = vbNullString Then
        MkDir "\\FILEPATH\FILEPATH\Database\Backup\" & strYear
        End If
        
    'If a year folder exists, check if a month folder exists inside. If not, create one.
    Dim Path2 As String
    Dim Folder2 As String
    Dim Answer2 As VbMsgBoxResult
    Path = "\\FILEPATH\FILEPATH\Database\Backup\" & strYear & "\" & strMonth
    Folder = Dir(Path, vbDirectory)
    If Folder = vbNullString Then
    MkDir "\\FILEPATH\FILEPATH\Database\Backup\" & strYear & "\" & strMonth
    End If
        
    'If a year folder exists and a month folder exists inside, check if a day folder exists inside. If not, create one.
    Dim Path3 As String
    Dim Folder3 As String
    Dim Answer3 As VbMsgBoxResult
    Path = "\\FILEPATH\FILEPATH\Database\Backup\" & strYear & "\" & strMonth & "\" & strDay
    Folder = Dir(Path, vbDirectory)
    If Folder = vbNullString Then
    MkDir "\\FILEPATH\FILEPATH\Database\Backup\" & strYear & "\" & strMonth & "\" & strDay
    End If
        
    'Determine your final destination (lol)
    strDestinationBE = "\\FILEPATH\FILEPATH\Database\Backup\" & strYear & "\" & strMonth & "\" & strDay & "\" & "Database_BE(" _
    & Format(Now, "yyyy-mm-dd hhnn") & ").accdb"
    Debug.Print "Back End File Path/Name: " & strDestinationBE
    
    strDestinationFE = "\\FILEPATH\FILEPATH\Database\Backup\" & strYear & "\" & strMonth & "\" & strDay & "\" & "Database_FE(" _
    & Format(Now, "yyyy-mm-dd hhnn") & ").accdb"
    Debug.Print "Front End File Path/Name: " & strDestinationFE
    'Flush the cache of the current database
    DBEngine.Idle
    
    'Create a file scripting object that will backup the db
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    oFSO.CopyFile strSourceBE, strDestinationBE
    oFSO.CopyFile strSourceFE, strDestinationFE
    Set oFSO = Nothing
        
    'Compact the new file, ...
    Name strDestinationBE As strDestinationBE & ".cpk"
    DBEngine.CompactDatabase strDestinationBE & ".cpk", strDestinationBE
    Kill strDestinationBE & ".cpk"
    
    Name strDestinationFE As strDestinationFE & ".cpk"
    DBEngine.CompactDatabase strDestinationFE & ".cpk", strDestinationFE
    Kill strDestinationFE & ".cpk"
    
    'Notify users
    MsgBox "Backup files created successfuly." & vbCrLf & vbCrLf & "Back End: " & vbCrLf & strDestinationBE & vbCrLf & vbCrLf & "Front End: " & vbCrLf & strDestinationFE, vbInformation, "Backup Completed!"
    
errExit:
    Exit Sub
    
errHandler:
    MsgBox Err.Number & ": " & Err.Description
    Resume errExit
       
End Sub

I hope somebody finds this helpful.

[theDBguy]

Originally Posted by mhorner

Good morning DBGuy, thanks for your help. I did get this working, but abandoned the powershell script and tackled both the front and back end backup routines directly through VBA. I ended up using the link you sent previously as a foundation and added some folder detection and creation to match the folder structure I already have established.

The way my backup folders structured are:
G:\Database\Backup\[Year]\[Month]\[Day]\Database Back End ([Year]-[Month]-[Day]-[Time])

Every time the backup routine is run, this looks into the G:\Database\Backup folder to determine if a folder already exists for the year of today's date. If not, it will create one. If yes, it will determine if a folder exists inside the year folder whose name is the month of today's date. If not, it will create one. If yes, it will determine if a folder exists inside the month folder whose name is the day of today's date. If not, it will create one. It then proceeds to backup both the front and back end databases into said folder, then prompts the user that the backup was successfull (and states the name and file path of the backups).
...
I hope somebody finds this helpful

Hi. Congratulations! Glad to hear you got it sorted out. However, as I said, I wasn't very familiar with PS, so I was hoping it would be nice to see the PS solution too, just in case others could use it as well. Can you share it?

[Gasman]

Thanks for posting that.
However I *think* that backs up the BE as the FE? as you are using strSource each time.?

Wouldn't want that to catch you out further down the road.

[Micron]

Everyone will forever have the server folder mapped to G? This isn't always the case in a corporate environment. Maybe use UNC path instead?? I would probably turn off the Debug lines also. Code is well documented - your successor will thank you

[Minty]

Originally Posted by Micron

Everyone will forever have the server folder mapped to G? This isn't always the case in a corporate environment. Maybe use UNC path instead?? I would probably turn off the Debug lines also. Code is well documented - your successor will thank you

I agree with Micron, \\YourServer\Yourpath is much more reliable in the long term, unless you push out shares using a GPO or similar.

Nicely documented code !

[mhorner]

Originally Posted by theDBguy

Hi. Congratulations! Glad to hear you got it sorted out. However, as I said, I wasn't very familiar with PS, so I was hoping it would be nice to see the PS solution too, just in case others could use it as well. Can you share it?

I actually didn't figure out how to pass powershell commands through VBA. I am happy to share my powershell script (+ the required PS commands to call the script successfully):

First, an instance of powershell needs to be opened (or called from VBA):

Because of execution policies, the two lines below need to be executed in PS before you can successfully run a script:

set-executionpolicy -scope currentuser
a

Then you can run a script by changing the directory to the path holding the script:

PS \\FILEPATH HERE

Then you have to call the name of the script in the directory you've navigated to:

./backup.ps1

If trying to do this strictly through VBA, all 4 commands above need to be assembled into a single call line of VBA. This is where I was struggling:

'Need to first  "set-executionpolicy -scope currentuser"
'Then need to send the command "a" to confirm execution policy command
'Then need to change directory to point to the filepath "PS \\FILEPATH HERE"
'Then need to call powershell script (change FILE PATH HERE to the filepath of the PS script you want to call). I can only figure this command out.

x = Shell("POWERSHELL.exe -noexit CD " & "'FILE PATH HERE'", vbNormalFocus)

The powershell script responsible for backing up the files is:

## Variables
##Source Directory
$source="\\FILEPATH\FILEPATH\Database\Backup"

New-Item -ItemType Directory -Path "\\FILEPATH\FILEPATH\Database\Backup\$((Get-Date).ToString('yyyy-MM-dd'))"

write-host ### Backing up Database...
Pause

set folder=%date:~10,4%\%date:~4,2%\%date:~7,2%
copy-item "\\FILEPATH\FILEPATH\Database\DatabaseBE.accdb" "\\FILEPATH\FILEPATH\Database\Backup\$((Get-Date).ToString('yyyy-MM-dd'))" -force -recurse -verbose 
copy-item "\\FILEPATH\FILEPATH\Database\DatabaseFE.accdb" "\\FILEPATH\FILEPATH\Database\Backup\$((Get-Date).ToString('yyyy-MM-dd'))" -force -recurse -verbose 

write-host ### Backup Complete.
Pause

Originally Posted by Micron

Everyone will forever have the server folder mapped to G? This isn't always the case in a corporate environment. Maybe use UNC path instead?? I would probably turn off the Debug lines also. Code is well documented - your successor will thank you

Originally Posted by Minty

I agree with Micron, \\YourServer\Yourpath is much more reliable in the long term, unless you push out shares using a GPO or similar.

Nicely documented code !

Thank you both. I actually already specified the UNC path, but changed it here to remove the real file paths (removing propriety company info) so I can safely share. I updated the code above to reflect the UNC nomenclature.

Originally Posted by Gasman

Thanks for posting that.
However I *think* that backs up the BE as the FE? as you are using strSource each time.?

Wouldn't want that to catch you out further down the road.

Wow, terrific find. This could have been a catastrophe. I updated the code above to add a second source variable for the front end. I also updated all the variables to clarify which file they were associated with backing up (BE or FE) -- just to make things a little bit easier to follow.

Thanks again all for the kind words and help.

[theDBguy]

Hi. Thanks for posting the PS script. It may come handy in the future. Just as FYI, I am updating my website to include a line of code for backing up password-protected backend files. Good luck with your project.

[jdraw]

I found an old msg file (Oct'17) that involves Powershell and VBA after reading your topic headline.
I had a computer failure/disk replacement a year ago and could not find a reference in any of my databases, nor in any of the forums I frequent..
Anyway, I was able to run Powershell via a Shell command. It doesn't do much, but it was a thought when trying to resolve reading JSON with vba. (Isladogs has a solution for that) I have not pursued anything with Powershell after my computer failure.

Basic powershell script

<#CarsBasic.ps1
.DESCRIPTION
This script takes the cars.json file and reads it into memory
Converts it from Json, then selects id,manufacturer,year from the result
and exports the data to C:\Programs\CarsJack.csv as a csv file with header
#>
 (Get-Content C:\Programs\MendipDataSystems\JSONParser\Files\Cars.json -Raw |
 ConvertFrom-Json) | Select id,manufacturer,year |
  Export-CSV  c:\programs\CarsJack.csv -NoTypeInformation

Called it from this vba:

'---------------------------------------------------------------------------------------
' Procedure : ShellToPowershell
' Author    : mellon
' Date      : 05-Oct-2017
' Purpose   :This is an attempt to execute a powershell script CarsBasic.ps1
'
'---------------------------------------------------------------------------------------
'
Sub ShellToPowershell()

      Dim retval
10       On Error GoTo ShellToPowershell_Error

20     retval = Shell("powershell c:\users\mellon\Documents\PSScripts\Carsbasic.ps1 ", vbNormalFocus)
       
ShellToPowershell_Exit:
30       Exit Sub

ShellToPowershell_Error:
40       MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure ShellToPowershell of Module ModuleTesting_CanKill"
50       Resume ShellToPowershell_Exit
End Sub

Partial output (limited just to reduce length of post)

CarsJack.csv

"id","manufacturer","year"
"1","Nissan","2011"
"2","Oldsmobile","2004"
"3","Cadillac","2002"
"4","Hyundai","2006"
"5","Volkswagen","1990"
"6","Dodge","2003"
"7","Volkswagen","1994"
"8","Chevrolet","2004"
"9","Mitsubishi","2002"
"10","Pontiac","2008"
"11","Mercury","1995"
"12","Chevrolet","2012"
"13","Nissan","1997"
"14","Buick","2007"
"15","Mitsubishi","1989"
"16","Mitsubishi","1988"
"17","Ford","1989"
"18","BMW","1959"
"19","Ford","1987"
"20","Volvo","1998"
"21","Hyundai","1999"
"22","Chevrolet","1993"