05-20-2019, 11:46 AM
|
#1
|
Newly Registered User
Join Date: May 2018
Posts: 31
Thanks: 14
Thanked 0 Times in 0 Posts
|
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:
- set-executionpolicy -scope currentuser
- a
- ./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?
|
|
|
05-20-2019, 11:51 AM
|
#2
|
I知 here to help
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,424
Thanks: 58
Thanked 1,409 Times in 1,390 Posts
|
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.
__________________
Just my 2 cents...
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts. | To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts. | To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
|
|
The Following User Says Thank You to theDBguy For This Useful Post:
|
|
05-20-2019, 11:52 AM
|
#3
|
I知 here to help
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,424
Thanks: 58
Thanked 1,409 Times in 1,390 Posts
|
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...
__________________
Just my 2 cents...
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts. | To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts. | To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
|
|
The Following User Says Thank You to theDBguy For This Useful Post:
|
|
05-20-2019, 11:57 AM
|
#4
|
I知 here to help
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,424
Thanks: 58
Thanked 1,409 Times in 1,390 Posts
|
Re: Powershell Commands through VBA
__________________
Just my 2 cents...
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts. | To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts. | To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
|
|
The Following User Says Thank You to theDBguy For This Useful Post:
|
|
05-21-2019, 08:55 AM
|
#5
|
Newly Registered User
Join Date: May 2018
Posts: 31
Thanks: 14
Thanked 0 Times in 0 Posts
|
Re: Powershell Commands through VBA
Quote:
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.
Quote:
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.
Quote:
Originally Posted by theDBguy
|
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.
|
|
|
05-21-2019, 09:04 AM
|
#6
|
I知 here to help
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,424
Thanks: 58
Thanked 1,409 Times in 1,390 Posts
|
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!
__________________
Just my 2 cents...
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts. | To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts. | To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
|
|
05-24-2019, 03:51 AM
|
#7
|
Newly Registered User
Join Date: May 2018
Posts: 31
Thanks: 14
Thanked 0 Times in 0 Posts
|
Re: Powershell Commands through VBA
Quote:
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.
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.
Last edited by mhorner; 05-28-2019 at 05:33 AM.
|
|
|
05-24-2019, 06:31 AM
|
#8
|
I知 here to help
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,424
Thanks: 58
Thanked 1,409 Times in 1,390 Posts
|
Re: Powershell Commands through VBA
Quote:
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?
__________________
Just my 2 cents...
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts. | To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts. | To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
|
|
The Following User Says Thank You to theDBguy For This Useful Post:
|
|
05-24-2019, 06:50 AM
|
#9
|
Enthusiastic Amateur
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,507
Thanks: 439
Thanked 838 Times in 809 Posts
|
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.
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Please, please use code tag # when posting code snippets
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
|
|
The Following User Says Thank You to Gasman For This Useful Post:
|
|
05-24-2019, 07:15 AM
|
#10
|
AWF VIP
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,190
Thanks: 10
Thanked 226 Times in 214 Posts
|
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
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
|
|
|
The Following User Says Thank You to Micron For This Useful Post:
|
|
05-24-2019, 07:21 AM
|
#11
|
AWF VIP
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,437
Thanks: 166
Thanked 1,738 Times in 1,707 Posts
|
Re: Powershell Commands through VBA
Quote:
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 !
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
|
|
The Following User Says Thank You to Minty For This Useful Post:
|
|
05-28-2019, 06:02 AM
|
#12
|
Newly Registered User
Join Date: May 2018
Posts: 31
Thanks: 14
Thanked 0 Times in 0 Posts
|
Re: Powershell Commands through VBA
Quote:
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:
Code:
set-executionpolicy -scope currentuser
a
Then you can run a script by changing the directory to the path holding the script:
Then you have to call the name of the script in the directory you've navigated to:
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
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
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
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.
|
|
|
05-28-2019, 06:48 AM
|
#13
|
I知 here to help
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,424
Thanks: 58
Thanked 1,409 Times in 1,390 Posts
|
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.
__________________
Just my 2 cents...
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts. | To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts. | To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Last edited by theDBguy; 05-28-2019 at 07:40 AM.
|
|
|
05-28-2019, 07:37 AM
|
#14
|
Super Moderator
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,273
Thanks: 96
Thanked 2,030 Times in 1,977 Posts
|
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"
__________________
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Last edited by jdraw; 05-28-2019 at 07:44 AM.
|
|
|
Thread Tools |
|
Display Modes |
Rate This Thread |
Linear Mode
|
|
All times are GMT -8. The time now is 02:01 PM.
|
|