Access World Forums

Access World Forums (https://www.access-programmers.co.uk/forums/index.php)
-   Visual Basic (https://www.access-programmers.co.uk/forums/forumdisplay.php?f=68)
-   -   Powershell Commands through VBA (https://www.access-programmers.co.uk/forums/showthread.php?t=305144)

mhorner 05-20-2019 11:46 AM

Powershell Commands through VBA
 
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 05-20-2019 11:51 AM

Re: Powershell Commands through VBA
 
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 05-20-2019 11:52 AM

Re: Powershell Commands through VBA
 
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 05-20-2019 11:57 AM

Re: Powershell Commands through VBA
 
Hi. See if this previous discussion helps...

mhorner 05-21-2019 08:55 AM

Re: Powershell Commands through VBA
 
Quote:

Originally Posted by theDBguy (Post 1623731)
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.

Quote:

Originally Posted by theDBguy (Post 1623732)
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.

Quote:

Originally Posted by theDBguy (Post 1623733)
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 05-21-2019 09:04 AM

Re: Powershell Commands through VBA
 
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 05-24-2019 03:51 AM

Re: Powershell Commands through VBA
 
Quote:

Originally Posted by theDBguy (Post 1623907)
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.
Code:

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 05-24-2019 06:31 AM

Re: Powershell Commands through VBA
 
Quote:

Originally Posted by mhorner (Post 1624340)
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 05-24-2019 06:50 AM

Re: Powershell Commands through VBA
 
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 05-24-2019 07:15 AM

Re: Powershell Commands through VBA
 
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 05-24-2019 07:21 AM

Re: Powershell Commands through VBA
 
Quote:

Originally Posted by Micron (Post 1624365)
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 05-28-2019 06:02 AM

Re: Powershell Commands through VBA
 
Quote:

Originally Posted by theDBguy (Post 1624360)
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:
Code:

set-executionpolicy -scope currentuser
a

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

PS \\FILEPATH HERE
Then you have to call the name of the script in the directory you've navigated to:
Code:

./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:
Code:

'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:
Code:

## 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


Quote:

Originally Posted by Micron (Post 1624365)
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 :)

Quote:

Originally Posted by Minty (Post 1624366)
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.


Quote:

Originally Posted by Gasman (Post 1624363)
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 05-28-2019 06:48 AM

Re: Powershell Commands through VBA
 
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 05-28-2019 07:37 AM

Re: Powershell Commands through VBA
 
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
Code:

<#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:

Code:

'---------------------------------------------------------------------------------------
' 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)

Code:

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"



All times are GMT -8. The time now is 08:15 AM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World