SirReginaldCrumbly SirReginaldCrumbly - 1 year ago 83
Python Question

Python code for running ms-Access Module Subroutine

I am very new to programming and this is my first question on stackoverflow. I am trying to make python open an .accdb file and run a subroutine which is already defined in Access. I manage to do it with Excel using this code:

import win32com.client
xl=win32com.client.Dispatch("Excel.Application")
xl.Visible=True
xl.Workbooks.Open(Filename="<mydirectory>\\open",ReadOnly=1)
xl.Application.Run("TestMe")
#...access spreadsheet data...
xl.Workbooks(1).Close(SaveChanges=0)
xl.Application.Quit()
xl=0


The Sub TestMe looks like this:

Sub TestMe()
MsgBox "Hi there"
End Sub


Running the Python code promtly launches Excel, opens the file open.xlsm and displays a messagebox. So far so good. Thanks to: Need skeleton code to call Excel VBA from PythonWin

I've modified the code to try to acheive the same with Access. I made a new .accdb file called "testdb" and copied the above subroutine "TestMe" into a VBA module. The modified python code looks like this:

import win32com.client
xl=win32com.client.Dispatch("Access.Application")
xl.Visible=True
xl.OpenCurrentDatabase("<mydirectory>\\testdb.accdb")
xl.Application.Run("TestMe")
#...access spreadsheet data...
xl.Workbooks(1).Close(SaveChanges=0)
xl.Application.Quit()
xl=0


The main change is that "Workbooks.Open" has changed to "OpenCurrentDatabase". I first tried to find something more similar, like "Databases.Open", but with no luck. Running the new code launches Access and opens the file testdb.accdb, but that's it, no messagebox appears. The only Console output I can imagine is of any interest is:

xl.Application.Run("TestMe")
File "<COMObject <unknown>>", line 14, in Run

result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes) + args)
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2147352562), None)


I am quite at a loss. Any help would be greatly appreciated!

Answer Source

Consider creating a new Access macro object with a RunCode action that calls the function in the module. Then, call the macro in Python's Windows COM API using use the DoCmd.RunMacro method.

MACRO

Macro
RunCode: TestMe()

NOTE: Only functions can be referenced with RunCode not subroutines unless you create a VBA module function that calls the subroutine: Call SubroutineName:

Python

import win32com.client
ac = win32com.client.Dispatch("Access.Application")
ac.Visible=True
ac.OpenCurrentDatabase("<mydirectory>\\testdb.accdb")
ac.DoCmd.RunMacro('MacroName')

ac.DoCmd.CloseDatabase
ac = None
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download