Running Access Macro in Powershell

Question

Running Access Macro in Powershell

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?

powershell

ms-access

macros

asked on Stack Overflow Jul 20, 2015 by

Gareth • edited Jul 20, 2015 by

Gareth

2 Answers

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

answered on Stack Overflow Jul 24, 2015 by

Jonathan H • edited Jul 24, 2015 by

Jonathan H

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

answered on Stack Overflow Jul 23, 2015 by

Jeno Laszlo • edited Jul 23, 2015 by

Jeno Laszlo

User contributions licensed under CC BY-SA 3.0