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?

@Vesper Sorry should have clarified, no parameters are expected by the procedure. — Gareth
– Gareth, Commented Jul 20, 2015 at 13:46
Do you run into the same problem with a minimal procedure ... $Access.Run("SayHello") where SayHello is a public sub which includes only MsgBox "Hello Word!" — HansUp
– HansUp, Commented Jul 20, 2015 at 13:58
Nuts! I just tested the VBScript equivalent of your PowerShell code and can't see why there should be a problem. Sorry, I'm not proficient with PowerShell. Can you show us the code from your SomeProc procedure? If it's a monster, please show us a minimal version which triggers the same error. — HansUp
– HansUp, Commented Jul 20, 2015 at 14:21
@HansUp thanks - I'm actually migrating from vbscript to powershell and the procedure and vbscript equivalent defo works! That's the annoying part so assume it's a powershell issue — Gareth
– Gareth, Commented Jul 20, 2015 at 14:26

Jonathan H · Accepted Answer · 2015-07-24 19:26:19Z

4

+100

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

edited Jul 24, 2015 at 19:26

answered Jul 24, 2015 at 16:43

Jonathan H

3882 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jeno Laszlo · Accepted Answer · 2015-07-23 18:58:02Z

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

edited Jul 23, 2015 at 18:58

answered Jul 23, 2015 at 15:58

Jeno Laszlo

2,29122 silver badges40 bronze badges

10 Comments

Gord Thompson Over a year ago

There is definitely an overload to the .Run() method that takes only one argument: the name of the procedure. As I mention in my comments to the question, I had no problems with the asker's sample code when I tried it on a couple of my machines.

Jeno Laszlo Over a year ago

I created a C# project and referenced the dll. I see only one method. I don't see overloaded ones. The sample didn't work for me until I passed in all arguments. What is the full name of the assembly you are using? Maybe the issue is related to versions.

Jeno Laszlo Over a year ago

$Access.GetType().GetMethod("Run").Count # will give me 1

Jeno Laszlo Over a year ago

$Access.GetType().GetMethod("Run").GetParameters() | Format-Table # gives me a a list of 31 mandatory arguments

TheMadTechnician Over a year ago

I'll chime in here and state that only the first argument to the Run() method is required, as stated in the TechNet page regarding it.

|

Collectives™ on Stack Overflow

Running Access Macro in Powershell

2 Answers 2

Comments

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related