how to Run Access marcos from Power Shell

[vvwstcat]

(I put this post on the Macros thread, but seems there is nobody interested in this problem. I therefore unsubscribed this thread and put it in the Access General Thread. I am a newbie, I dont know how to delete this thread, would some experts help me. thanks)



I want to do some automation using power shell and MS Access.
The objective is to use PowerShell to Call up MS Access, then run a particular macro that has been defined in a MS Access database.

$mypath = (get-location).Path
$myDB = $mypath + "\US StockDB.accdb"
$MsAccess = New-Object -ComObject Access.Application
$MsAccess.OpenCurrentDatabase($myDB)


$MsAccess.Run("MacImpStock")

I got error as below:
Exception calling "Run" with "1" argument(s): "Microsoft Access cannot find the procedure 'MacImpStock.'"
At line:1 char:1
+ $MsAccess.Run("MacImpStock")
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: ( [], MethodInvocationException
+ FullyQualifiedErrorId : COMException


The Access Macro details is shown in the attached file.
Appreciate your expert advice. Many thanks.

[Micron]

Never have seen code like that. What is it?
Can suggest basic trouble shooting technique though - output the path string variable (or check it via your preferred method) and ensure it is what you think it is. Mostly I've worked on Excel from Access, but either way I'd use automation as opposed to whatever that is. Since the code is unfamiliar to me, I'll also take a wild guess that it should be
.RunMacro "macroName" rather than .Run ("MacroName"). () are usually only used where a function return value is being assigned, or with the Call statement.

[pbaldy]

FYI I'm deleting your duplicate thread and moving this one to the general forum per your desire. I don't get the sense that many people here know much about Power Shell, me among them. You might have better luck on a Power Shell forum.

[vvwstcat]

thanks Paul.

[June7]

I have done this in VBScript. Adapting for your object variable, consider:

Use Run for VBA Sub procedures. Example for a Sub that expects 3 arguments.

$MsAccess.Run "HelloTest", "June", "Home", "Late"

Use RunMacro for macros.

$MsAccess.DoCmd.RunMacro "Macro"

[vvwstcat]

Hi June7,
Thank you very much for providing the solution. [$MsAccess.DoCmd.RunMacro "Macro"] does not work, but it is very closed.
I searched the internet and found the solution. Though I've got the solution, but I think it is very weird - how come MS did not unify the command syntaxes?!
The correct syntax is:
$MsAccess.Application.DoCmd.RunMacro ("Macro")

Thanks a lot.