Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Tuesday, April 16, 2013 4:22 PM
I had a program written in VBA which called a PowerShell script to create Exch4010 mailbox accounts for new users. Worked fabulous with Access 2003. We moved our Databases to a newer server with Access 2010 on it. I have checked reference libraries and anything pertaining to the code - everything looks good. I then consulted the Microsoft boards for calling a PS Script from Access2010 but their solution does not work. If anyone has any suggestions or solutions - much appreciated! Access 2010 suggestion - does not work: |
| The actual code in the file does run and work from the new server - so I just need to get the Call fixed. |
All replies (13)
Thursday, April 18, 2013 7:08 AM âś…Answered | 1 vote
Hi,
I have a snippet of code in my Asset database, which works well in Access 2010:
DoCmd.SetWarnings False
lngHWnd = Shell("powershell.exe " & CurrentProject.Path & "\concatenate.ps1")
WaitWhileRunning (lngHWnd)
(The only thing I miss there is to have a status bar showing the execution status of the script.)
Maybe, you missed something else, like adjusting Trust Center settings in MS Access to point to the proper folder.
Do you get any error message?
Eventually the hard coded path to PowerShell is the culprit - did you try explicitly with the 32 Bit version of PowerShell (path %SystemRoot%\syswow64\WindowsPowerShell\v1.0\powershell.exe)?
Best greetings from Germany
Olaf
Tuesday, April 16, 2013 4:35 PM
Are you sure its not your quotes or something? Looking from the first example you have double quotes on script name..
Call Shell("C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.exe -command ""c:\scripts\NewMailbox.ps1""", vbMaximizedFocus)
Tuesday, April 16, 2013 4:51 PM
Tried it both ways using this as reference: http://social.technet.microsoft.com/Forums/en-US/winserverpowershell/thread/9e07c99e-65e8-460a-8dbf-193e790a0714/
Thursday, April 18, 2013 6:44 AM
Hi,
How about:
Call Shell("C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.exe executionpolicy bypass -command c:\scripts\NewMailbox.ps1", vbMaximizedFocus)
Regards,
Yan Li
If you have any feedback on our support, please click here .
Cataleya Li
TechNet Community Support
Thursday, April 18, 2013 1:35 PM
I have the execution policy set on the machine permanently to bypass for admin.
Thursday, April 18, 2013 2:50 PM
Well without execution policy properly set you cannot execute any scripts. What do you mean with "for admin"? Do you run Access "As Administrator" or did you disable UAC?
Access has also some security settings in Trust Center, which could cause issues with external commands. And sometimes (we had this in the forum) 64Bit version of PowerShell does not work for certain scripts - especially in interaction with 32Bit Office this could be something at least to test).
Best greetings from Germany
Olaf
Thursday, April 18, 2013 3:41 PM
Olaf - I took a look at this. The server it was moved from was a 2003 R2 64bit to a 2003 R2 32bit. We actually have a driver call by a piece of software that restricts our ability to upgrade the server to a 64bit or 2008 R2 server.
There is no error message in the Access run - when I run the code from PS line it says it cannot find the file - the file exists and the content of the file runs perfect. I created a folder, same name as old server, same path - I even went so far as to set permissions for the admin account for that folder.
That server has the following set for the execution policy: Set-ExecutionPolicy -scope CurrentUser -executionpolicy bypass -force
Our CurrentUser is the admin account as only the admin account can create AD,EXCH accounts remotely.
Friday, April 19, 2013 6:36 AM
"Cannot find the file" - would this refer to the script?
Did you try - as in my example - to run the powershell command without any -file or -command parameter (i.e. powershell.exe c:\scripts\NewMailbox.ps1)?
Bet greetings from Germany
Olaf
Friday, April 19, 2013 2:40 PM
I tried this - it did not work.
From a PS comand line, I can run Invoke-command c:\scripts\NewMailbox.ps1 and get a mailbox made on the EXCH2010 server.
So I know it's not any kind of server-to-server commuinication issue. Definitely some kind of VBA Access 2010 change.
Friday, April 19, 2013 6:29 PM
Could you add some tracking code to your PowerShell script (like bringing up messages to the screen on various segments of the script) to see if the script is executed at all?
The PowerShell command line is executed with the same credentials as Access?
What happens, if you turn off User Account Control for testing?
Best greetings from Germany
Olaf
Friday, April 19, 2013 7:00 PM
The log in to the access database is on the Admin account - in fact, I have written code that if I log in as me (DBA) even though I have full priviledges, it will error and not even open the form that controls account administration. That reminds me that the PS script can't run because it doesn't see an Admin log on.
I will work on this over the weekend. See if it's even getting into the file. Doesn't look like it though.
Wednesday, April 24, 2013 5:32 AM
Hi,
Just checking in to see if the suggestions were helpful. Please let us know if you would like further assistance.
If you have any feedback on our support, please click here .
Cataleya Li
TechNet Community Support
Thursday, April 25, 2013 7:41 PM
Here's what I found out.... by making these changes - it does want to run - taking about 10 seconds to run. By mistake when I was testing I looked elsewhere for a moment and when I looked back, on the bottom task bar PS had loaded and was running script for the mailbox. Thankfully, this program is run by IT staff so I just wrote them to please wait for the PS window to load, run and close then move forward.