How to run a MS Access sub/function with parameters from Python?

Question

I can run MS Access macros (which then run a MS Access VBA function) with Python as discussed here. However, Access macros seem not to be able to use parameters.

Is there any possibility to run a Access VBA sub or function directly from Python and pass parameters to it? I tried application.run.

VBA Code:

Public Function doNothing()
    MsgBox "Test"
End Function

Access Macro3: Runs exactly this Function

Python:

import win32api
from win32com.client import Dispatch

objAccess = Dispatch("Access.Application")
objAccess.Visible = True
objAccess.OpenCurrentDatabase(destinationName)

#Working with macro: (but no parameters possible)
objAccess.DoCmd.RunMacro('Macro3') 

#Not working: direct function call
#objAccess.Run('doNothing')

#Also not working:
#objDB = objAccess.CurrentDb()
#objDB.Run('doNothing')

objAccess.Application.Quit()

the question needs sufficient code for a minimal reproducible example — darren
– darren, Commented Jan 18, 2024 at 17:46
Are you able to run the Access code from another VBA host such as Excel for example? — Tim Williams
– Tim Williams, Commented Jan 19, 2024 at 7:02
Thanks, Tim, this was the right hint to eventually find the solution I describe below. — meisterhell
– meisterhell, Commented Jan 19, 2024 at 12:15

meisterhell · Accepted Answer · 2024-01-19 12:35:53Z

This is how I got it working: First, I tried to get the call to Access VBA running from Excel.

Excel VBA Code:

Public Sub openAccess()

Dim acc As Access.Application
Set acc = New Access.Application
    With acc
        .Application.Visible = True
        .OpenCurrentDatabase "C:\Temp\test.accdb"
         With .Application
            .Eval ("doNothing()")
         End With
        .Quit
    End With
Set acc = Nothing

End Sub

Then I could find a solution for Python as well.

Python Code:

import win32api,time
from win32com.client import Dispatch
from datetime import date, datetime
  
destinationName = "C:\Temp\test.accdb"
    
#now get the db object and open the  db
objAccess = Dispatch("Access.Application")
objAccess.Visible = True       
objAccess.OpenCurrentDatabase(destinationName)
    
    
#calling a function does work
objAccess.Application.Eval('doNothing()')
    
objAccess.Application.Quit()

Now, if we need to use function parameters, note that in this call Eval() does take parameters as string only, e.g. for a date parameter:

strDay = date.today().strftime("%m/%d/%Y")
objAccess.Application.Eval('doNothing("' + strDay + '")')

Hence, afterwards the Access VBA code needs to translate the input date string back into a date again:

Public Function doNothing(inputDate As String)
    MsgBox CDate(inputDate) - 10
End Function

Collectives™ on Stack Overflow

How to run a MS Access sub/function with parameters from Python?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related