How can msaccess and its properties be made available after using pythonnet for instantiation?

Question

I have used pythonnet to instantiate MS Access. Once the instance is created, I intended to open a file using OpenCurrentDatabase but I subsequently discovered that a python "object" is converted to a "System.object" by pythonnet. My code is (mostly thanks to Gord Thompson):

Code

import clr

clr.AddReference('System')

t = System.Type.GetTypeFromProgID('Access.Application')

access = System.Activator.CreateInstance(t)
print(access)
access.OpenCurrentDatabase(r'C:\Users\Test.accdb')
access.Visible = True
num = 1
rtrn_val = access.Run('TestLink', num)
print(rtrn_val)

The code successfully instantiates MS Access but I am then unable to manipulate the object in what I would have thought was the normal way owing (I think) to the "object" to "System.__ComObject" conversion that occurs and can be seen in the error trace.

Error trace

>>> import clr
>>> clr.AddReference('System')
<System.Reflection.RuntimeAssembly object at 0x0000000004163CC0>
>>> t = System.Type.GetTypeFromProgID('Access.Application')
>>> access = System.Activator.CreateInstance(t)
>>> print(access)
System.__ComObject
>>> access.OpenCurrentDatabase(r'C:\Users\Test.accdb')
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
AttributeError: '__ComObject' object has no attribute 'OpenCurrentDatabase'

My end game is to do the following:

In Python:

download information from a website (mostly using selenium)
send the information from the website to MSAccess by calling a VBA function from Python

In Access:

after being initiated by Python, run a bunch of routines to process the downloaded data and insert into tables
return a value to the Python code that originally called the VBA function

I am hoping that there is a simple explanation that someone can give me to advise how I can reference the System.__ComObject.something.OpenCurrentDatabase and System.__ComObject.something.Run(Access VBA code, args). I appreciate anyone's help on this.

I would prefer to not use IronPython as I need to make use of other Python modules (eg Selenium) which I have not been able to load in IronPython due to pip problems with IronPython (and maybe they're not compatible with IronPython)
should I have to use IronPython I will need to call functions between CPython and IronPython and after much reading on the web I am still unsure how this can be achieved

you can use sql to pass/retrieve data between python and ms-access, here is example using pythonnet: https://stackoverflow.com/questions/37712307/pyodbc-read-primary-keys-from-ms-access-mdb-database/37712996#37712996 — denfromufa, Aug 25 '17 at 21:16
The Acess [Application](https://msdn.microsoft.com/en-us/vba/access-vba/articles/application-object-access) object supports an [Eval](https://msdn.microsoft.com/en-us/vba/access-vba/articles/application-eval-method-access) method, which allows you to evaluate an arbitrary VBA expression. I can't think of any reason why this shouldn't be available via pythonnet (but I haven't tested; hence the comment instead of an answer). — Zev Spitz, Apr 30 '18 at 20:59

Gord Thompson · Answer 1 · 2017-08-28T16:41:58.317

I got the following code to work with

Python 2.7.13, 32-bit
pythonnet 2.3.0
Access 2013, 32-bit

import clr
import sys
print(sys.version)

sys.path.append(r"C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Access\15.0.0.0__71e9bce111e9429c")

clr.AddReference("Microsoft.Office.Interop.Access")
import Microsoft.Office.Interop.Access as Access
access = Access.ApplicationClass()
access.OpenCurrentDatabase(r"C:\Users\Public\Database1.accdb")

# (the currently-opened database contains the following function)
#
# Public Function AddTwoLongIntegers(i As Long, j As Long) As Long
#     AddTwoLongIntegers = i + j
# End Function

function_returned = access.Run("AddTwoLongIntegers", 4, 10)
print(function_returned)  # a tuple: (14, 4, 10)
print("The sum is {0}".format(function_returned[0]))  # just the return value

access.Quit()

re: Python3

I also tried the above code with Python 3.6.2 (32-bit) but it did not work. I could run a Sub that took no arguments, but whenever I tried to call a Sub or a Function with arguments the Python code failed with

IndexError: tuple assignment index out of range

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "C:/Users/Gord/PycharmProjects/pythonnet_test/main.py", line 20, in <module>
    function_returned = access.Run("AddTwoLongIntegers", 4, 10)
SystemError: <bound method 'Run'> returned a result with an error set

This currently appears to be an issue with Python 3.5 and later, as discussed here.

Thank you once again for your contribution Gord. I got the returned value that I wanted but also the "IndexError: tuple assignment index out of range error". I spent a fair bit of yesterday trying to get around that error with different versions of python and the like but was not able to successfully deal with it. I think I might be better off removing my MSAccess VBA interface and doing it all through python now that I have some familiarity with it. My experience with IronPython, pythonnet and win32com have all, in one way or another, been disappointing in accomplishing what I am after. — mrduncle1, Aug 29 '17 at 02:48

How can msaccess and its properties be made available after using pythonnet for instantiation?

Code

Error trace

In Python:

In Access:

1 Answers1