pywintypes.com_error: Application.Run does not work for calling VBA module from MS Access using Access.Application

[s-mcm]

Issue:

I am trying to call and execute VBA modules that are stored within my MicroSoft Access database. However, I cannot get it to work and do not understand why. Any help would be appreciated. This is the sample code:

Sample code:

import win32com.client

dbPath = r"C:\some\path\to your database\Database.accdb"

# Open Access application
access = win32com.client.Dispatch("Access.Application")

# Open the database
access.OpenCurrentDatabase(dbPath)

# Run the VBA function
result = access.Run("HelloWorld")

# Close database and quit Access
access.CloseCurrentDatabase()
access.Quit()

VBA module in Access

Public Function HelloWorld() As String
    HelloWorld = "Hello from Access, world!"
End Function

Error message:

Exception has occurred: com_error
(-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2147352562), None)
  File "C:\some path\script.py", line 13, in <module>
    access.Run("HelloWorld")
    ~~~~~~~~~~^^^^^^^^^^^^^^
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2147352562), None)

Extra:

• I'm using both python 64-bit and MS Access 64-bit
• win32com.client.DispatchEx produces the same error
• I made essentially the same code in a C# console app. And it executed the VBA module with no issues. Not sure what the difference is 🤷‍♂️
• Applicaiton.Run does not work for Access.Application #2535 seems to be the same error with no solutions. I cannot find any other similar issues on the internet.
• Confirm HelloWorld is in a regular Module (not class/form/report)
• Use access.Visible = True and time.sleep(2) to make sure Access is ready
• Check References in VBA editor