Running Access Macro in Powershell

Question

I'm trying to run an Access 2010 macro in PowerShell (v4.0 Windows 8.1) with the below code:

$Access = New-Object -com Access.Application

$Access.OpenCurrentDatabase("SomePath", $False, "Password")
$Access.Run("SomeProc")
$Access.CloseCurrentDatabase()
$Access.Quit()

[System.Runtime.InteropServices.Marshal]::ReleaseComObject($Access)
Remove-Variable Access

I get an error on the line $Access.Run("SomeProc") that there's not enough parameters specified:

Exception calling "Run" with "1" argument(s): "Invalid number of parameters. (Exception from HRESULT: 0x8002000E (DISP_E_BADPARAMCOUNT))"

The procedure SomeProc does not require any parameters.

I've read the msdn article on the run method and only one parameter is required.

I've also tried this workaround which also failed to work for an unrelated reason.

Does anyone know what the cause of the error could be and how to get the method working?

Answer 1

This is a driver issue where the OLEDB libraries aren't loading correctly.

I was able to reproduce your error exactly, and I was able to work around it by opening Powershell from your SysWow directory instead of System32.

Try opening this version of Powershell (you'll have to run set-executionpolicy again), and see if it'll execute your script.

%SystemRoot%\syswow64\WindowsPowerShell\v1.0\powershell.exe

Helpful link: https://social.msdn.microsoft.com/Forums/en-US/4500877f-0031-426e-869d-bda33d9fe254/microsoftaceoledb120-provider-cannot-be-found-it-may-not-be-properly-installed?forum=adodotnetdataproviders

Answer 2

The C# signature is something like this:

public object Run(string Procedure, ref object Arg1, ... ref object Arg30) ...

It means that COM the Arg optional arguments are not optional in .NET because they are explicitly marked as [ref]. You need to provide all 32 args even if you don't use them.

---

Assuming you have the following VBA code:

Public Sub Greeting(ByVal strName As String)
 MsgBox ("Hello, " & strName & "!"), vbInformation, "Greetings"
End Sub

You can either use call it like this:

$Access = New-Object -com Access.Application
$Access.OpenCurrentDatabase("Database1.accdb")
$runArgs = @([System.Reflection.Missing]::Value) * 31
$runArgs[0] = "Greeting" #Method Name
$runArgs[1] = "Jeno" #First Arg
$Access.GetType().GetMethod("Run").Invoke($Access, $runArgs)

In your case it will be:

$runArgs = @([System.Reflection.Missing]::Value) * 31
$runArgs[0] = "SomeProc" 
$Access.GetType().GetMethod("Run").Invoke($Access, $runArgs)

I would probably try to add a helper to the access object:

Add-Member -InputObject $Access -MemberType ScriptMethod -Name "Run2" -Value {
    $runArgs = @([System.Reflection.Missing]::Value) * 31
    for($i = 0; $i -lt $args.Length; $i++){ $runArgs[$i] = $args[$i] }
    $this.GetType().GetMethod("Run").Invoke($this, $runArgs)
}

Then you can use Run2 as you would expect:

$Access.Run2("Greeting", "Jeno")
$Access.Run2("SomeProc")