Python Code For Running Ms-access Module Subroutine

January 15, 2024 Post a Comment

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.

Solution 1:

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.clientac= win32com.client.Dispatch("Access.Application")
ac.Visible=True
ac.OpenCurrentDatabase("<mydirectory>\\testdb.accdb")
ac.DoCmd.RunMacro('MacroName')

ac.DoCmd.CloseDatabaseac= None

Solution 2:

I am by no means an expert in Python but have reasonable familiarity with Access and Excel VBA. If I knew Python better and earlier in my amateur programming career I would never have attempted to do what I set out to do below using any VBA code at all. Given the investment in time in VBA code I had to find a way....

I have spent the last several days/week trying to find a way to talk Python->Access VBA with the following requirements:

call Access VBA function from Python
send parameter to Access VBA function from Python
return value from Access VBA function to Python

I made attempts with clr using pythonnet and IronPython and found more and more confusing and unclear error messages and exceptions. I tried the above suggested DoCmd.RunMacro method but Access macros do not return values. Tried a new(ish) version of Access macros (2010) called data macros which do have an action called SetReturnVar but they do not talk to VBA unless through the more traditional macros and as I mentioned above, traditional macros do not return values. Today I read some Microsoft documentation (Access Application.Run method) more carefully:

Access Application.Run method

Baca Juga

I am not completely aware of the implications of that statement "you can't set a reference to an individual Microsoft Access database from any application other than Microsoft Access" but it occurred to me that a lot of the Python<->Office Application articles seemed more successful when talking Python<->Excel VBA. I reasoned that because I had been able to run Excel VBA <-> Access VBA in the past and if Python <-> Excel VBA worked as well as I had read, then a solution (albeit convoluted) seemed possible (I think the proper programmers call it a hack).

After about 1 1/2 hours of cutting/pasting code snippets and debugging:

Python

from win32com.client import Dispatch

FILELOC = r'C:\Users\Desktop\PyExcel.xlsm'
PROGNAME='Excel.Application'
num = 4#open excel workbook containing VBA code#...could do more to ensure excel isn't already running
xl = Dispatch(PROGNAME)
xl.Visible = True#open excel file containing the VBA code#...could do more to check if file is already open, etc 
xl.Workbooks.Open(Filename=FILELOC)
#call to VBA code within excel
rtrn_int = xl.Run("RunCOMObject", num)

#print return valueprint(rtrn_int)

#Quit excel-this doesn't work very well and there are articles about#Python or the COM object not being able to actually remove Excel#from the task manager
xl.Quit()

Excel VBA

OptionExplicitPrivateConst ACCESS_FILELOC AsString = "C:\Users\Desktop\Test.accdb"PrivateConst TEMP_FILELOC AsString = "C:\Users\Desktop\TestTemp.accdb"Function RunCOMObject(intNum AsInteger) AsIntegerDim objAcc AsObject, objProject AsObjectDim accAppl As Access.Application
    Dim MyAppl AsString

    MyAppl = "Access.Application"IfNot IsRunning(MyAppl) Then'Access not running, simply start 'up Access and open fileSet accAppl = CreateObject(MyAppl) 'start Access
        accAppl.Visible = True
        accAppl.OpenCurrentDatabase (ACCESS_FILELOC) 'open fileElse:  'Access is runningOnErrorResumeNextSet accAppl = GetObject(, MyAppl)  'assign the running application 'to a variableOnErrorGoTo Err_File_Open 'use an error in attempting to rename 'the database of interest to determine if the open file is the 'desired file
        Name ACCESS_FILELOC As TEMP_FILELOC 'rename the file of interest
        Name TEMP_FILELOC As ACCESS_FILELOC 'file was successfully renamed 'therefore not openCall NoFileOrOther(accAppl, MyAppl)
    EndIfErr_File_Open:'Required Access file is open
    RunCOMObject = accAppl.Run("TestLink", intNum) 'run the VBA function in 'Access
    accAppl.CloseCurrentDatabase  'close database
    accAppl.Quit 'quit AccessSet accAppl = NothingEndFunctionFunction IsRunning(ByVal MyAppl AsString) AsBooleanDim applRef AsObjectOnErrorResumeNext'error occurs if GetObject is unable to find a 'running version of the applicationSet applRef = GetObject(, MyAppl) 'attempt to obtain the required 'application objectIfNot applRef IsNothingThen'if application is already runningSet applRef = Nothing
        IsRunning = TrueElse'application is not running
        IsRunning = FalseEndIfSet applRef = NothingEndFunctionSub NoFileOrOther(accAppl As Access.Application, MyAppl AsString)

    OnErrorGoTo Err_No_FileOpen
    If accAppl.CurrentProject.Name <> ""Then'Access active with another a 'different file openSet accAppl = CreateObject(MyAppl) 'start a new instance of Access
        accAppl.Visible = True
        accAppl.OpenCurrentDatabase (ACCESS_FILELOC) 'open fileEndIfExitSubErr_No_FileOpen:
    accAppl.OpenCurrentDatabase (ACCESS_FILELOC)  'in the event of Access 'being active without a database openEndSub

Access VBA

This is a completely trivial example and belies the amount of code I had already written in Access VBA to warrant this workaround but it serves to demonstrate the methodology

OptionCompare Database
OptionExplicitFunction TestLink(intNum AsInteger) AsInteger
    TestLink = intNum + 10EndFunction

Python Output:

Success!!!! The number was initially 4 in Python and was sent as a parameter to Excel and Access where it had 10 added to it before being returned via Excel to Python print(rtrn_int) = 14.

If anyone knows how to definitively (ie through similar rigour demonstrated above) send arguments from Python -> Access VBA and return a value to Python without using Excel VBA as an intermediary I would be very happy to hear from you. Alternatively methods using pythonnet referencing clr would be similarly appreciated.

lacucinadiadine