1

I would like to launch a MS Access macro from the command line (it's being run from another tool) to generate an output. Thing is, I'd prefer it if it could supress access from loading it's interface. I just want it to run the macro and shut down. Any thoughts on how to do this?

I've used a command line something like this so far:

"C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS" "S:\filepath\Database\database.accdb" /x Macro1
HansUp
  • 95,961
  • 11
  • 77
  • 135
wizard_draziw
  • 505
  • 1
  • 5
  • 17
  • Please explain what suppress Access from loading its interface means. – HansUp Jun 17 '15 at 15:42
  • Sure! Basically I just want it to do it's thing but seemingly invisible. No windows pop up. Just execute the macro and close once done. – wizard_draziw Jun 17 '15 at 16:05
  • Thanks. To me, that sounds like just hide the Access application window. I don't know anything about that other tool you mentioned. Can it run VBA code or shell out to a VBScript? – HansUp Jun 17 '15 at 16:11
  • Well it's not 100% important what the other tool is. It's actually "R". I'm using it to execute a system command line (ie same thing you would enter into CMD). Access will then export one of it's tables into CSV, where R will then pick up the CSV. – wizard_draziw Jun 17 '15 at 16:12
  • So then can you call a command line like `cscript.exe NewVBScriptFile.vbs`? – HansUp Jun 17 '15 at 16:14
  • PS. I'm well aware that R can just do an ODBC to Access, however unfortunately my access is 32bit and I need to run R in 64bit which, apparently even in 2015, is impossible.. so this is a workaround :) – wizard_draziw Jun 17 '15 at 16:14
  • Yes, something like that. I just don't want an access window to pop up, I just want it to do it's work and exit, unbeknownst to the user. – wizard_draziw Jun 17 '15 at 16:15
  • Well the ODBC thing is irrelevant. You can't run an Access macro from an ODBC connection under any circumstance. A macro can only be run from within an Access application session. – HansUp Jun 17 '15 at 16:16
  • You misunderstand--if I could ODBC I would just ODBC direct to the table, instead of have this workaround use the macro to export the table to a CSV – wizard_draziw Jun 17 '15 at 16:27
  • You're right, I did misunderstand. With the 64 bit Access ACE driver, you can create an ODBC connection from a 64 bit process to an Access db. Have you investigated that option? – HansUp Jun 17 '15 at 16:29
  • Wouldn't both programs need to be running in 64 bit? I only have access 32 bit and no option to get 64 bit – wizard_draziw Jun 17 '15 at 16:38

1 Answers1

0

You can use COM automation from VBScript to create an Access application instance, hide it from view, run your macro, and then shut down Access.

I saved the following script as RunAccessMacro.vbs and ran it in a command prompt window using this as the command line ...

cscript //NoLogo C:\share\vbscript\RunAccessMacro.vbs

This is RunAccessMacro.vbs ...

Option Explicit

Const cstrDbFile = "C:\share\Access\database1.mdb"
Dim accessApp

Set accessApp = CreateObject("Access.Application")
accessApp.Visible = False
accessApp.OpenCurrentDataBase cstrDbFile, False ' open shared
accessApp.DoCmd.RunMacro "Macro1"
accessApp.Quit
Set accessApp = Nothing
HansUp
  • 95,961
  • 11
  • 77
  • 135