1. Create a PowerShell script (.ps1)
Example: C:\Scripts\Get-UserInfo.ps1
Content sample:
param($User)
Get-ADUser -Identity $User | Select-Object Name,SamAccountName,Enabled
2. Enable script execution
Run PowerShell as Administrator:
Set-ExecutionPolicy RemoteSigned -Scope CurrentUser
3. Use VBA inside Access to execute PowerShell
Example using WScript.Shell:
Function RunPS(UserName As String)
Dim shell As Object
Dim cmd As String
Set shell = CreateObject("WScript.Shell")
cmd = "powershell.exe -ExecutionPolicy Bypass -File ""C:\Scripts\Get-UserInfo.ps1"" -User " & UserName
shell.Run cmd, 1, True
End Function
4. Capture PowerShell output back into Access
Use StdOut redirection:
cmd = "powershell.exe -ExecutionPolicy Bypass -File ""C:\Scripts\Get-UserInfo.ps1"" -User " & UserName & " > C:\Scripts\output.txt"
Then read output.txt into a form or table:
Function ReadOutput()
Open "C:\Scripts\output.txt" For Input As #1
Do Until EOF(1)
Line Input #1, txt
Debug.Print txt
Loop
Close #1
End Function
5. Call the VBA function from a form button
Private Sub btnRun_Click()
RunPS Me.txtUser
ReadOutput
End Sub
6. Recommended folder structure
⭐ C:\Scripts\AccessAutomation\
⭐ Keep all PS1 scripts in one location
⭐ Give Access only read/execute permissions for security
7. Productivity automation tips
⭐ PowerShell:
• F8 to run selected code in ISE
• Up/Down arrows to recall command history
⭐ Access VBA editor:
• F5 run
• F9 toggle breakpoint
• Ctrl+G immediate window
⭐ Use param blocks in PowerShell so Access can send multiple arguments
param($User,$Ticket,$Date)
8. Language improvement tips
Correction: “I would like to automate PowerShell with an Access application.”
Improved: “I want to integrate PowerShell automation into a Microsoft Access application.”
Reason: “Integrate automation” is clearer and more technical.