Calling Access Subroutine from Python

Question

I am trying to run a sub in Access VBA from Python but it looks like I am facing an issue I do not quite manage to understand. Below the code that correctly opens the database (Access) but fail to run the VBA code

import os, os.path
import win32com.client

ac = win32com.client.Dispatch("Access.Application")
ac.Visible=True
ac.OpenCurrentDatabase(os.path.abspath("C:\Database.accdb"))
ac.Application.Run("Database.accdb!module3.subpycall")

The sub I am calling is quite straight forward and it is calling a click event of a button. The code of the sub can be found below

Sub subpycall()
    Call cmdUniverse_Click
End Sub

When running python,as per the above code, Access opens correctly, the main forms opens correctly but it returns the following error. Can anyhone help fixing this error?

File "C:\ON_CHAV.py", line 7, in ac.Application.Run("Database.accdb!module3.subpycall") File "<COMObject >", line 14, in Run File "C:\Users\PC\AppData\Local\Programs\Python\Python36\lib\site-packages\win32com\client\dynamic.py", line 287, in ApplyTypes result = self.oleobj.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes) + args) pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, None, "Database cannot find the procedure 'Database.accdb!module3.subpycall.'", None, -1, -2146825771), None)

Suki Singh · Accepted Answer · 2020-06-26 19:07:07Z

1

I have done this via VBS before (not sure if this is an option for you), but it's simple and can be scheduled in the Win TaskScheduler.

Step1:
In your Access module1 (?), make your 'calling' proc 'public' and (not needed), qualify where the button on a form is:

Public Sub subpycall()
    Call Form_frmMain.cmdUniverse_Click
End Sub

Step2:
Create a VBS file and put this code in it.  Use the Access object to open your database, wait for it to load 60 secs, and then call the subpycall proc.  Save as eg, subpycall.vbs:

Dim accessApp
set accessApp = createObject("Access.Application") 
accessApp.OpenCurrentDataBase("C:\CHAV.accdb")
accessApp.Visible = True
accessApp.DoCmd.OpenForm "FrmMain"
WScript.Sleep 60000
accessApp.Run "subpycall"
accessApp.Quit
Set accessApp = Nothing

Step3:
Create a bat file and put these lines in and save as subpycall.bat :

@echo off
cscript "C:\subpycall.vbs"

Step 4 (optional):
Schedule the subpycall.bat

Hope this helps.

answered Jun 26, 2020 at 19:07

Suki Singh

261 bronze badge

Sign up to request clarification or add additional context in comments.

1 Comment

Marco_sbt Over a year ago

Thanks @suki this is a nice workaround which works, but would love to call it from python as I already have a py code calling an xlsx vba code so has to be a way to call a VBA code in Access. In the meantime will test your solution and keep you posted. Thanks for the whole code!

Fabricio Fonseca · Accepted Answer · 2020-06-26 16:33:52Z

1

I'm not really sure how to solve your main question... but when I need to run VBA from python I create a VBS (VB script file) and call it from Python.

It may be an alternative to solve your problem, and will also release you from using msaccess.

Example Python calling:

  os.system('CSCRIPT my_script.vbs "' + os.getcwd() + '\\' + filename + '"')#filename is a paramenter I send to the scrit

Example VBS:

Dim filename, objWorkBook
'On Error Resume Next
filename = WScript.Arguments(0)
Set fso = CreateObject("Scripting.FileSystemObject")
temp_filename = Left(filename,InstrRev(filename,"\")) & "3A4Output.xlsb"
fso.CopyFile "my_file.xlsb", filename
'and so on...

answered Jun 26, 2020 at 16:33

Fabricio Fonseca

1395 bronze badges

1 Comment

Marco_sbt Over a year ago

Thanks @Fabrico this seems to be the same workaround proposed by Suki (below) which seems to work

Erik A · Accepted Answer · 2020-06-26 16:48:58Z

1

There are a lot of things to address here:

Run needs a function that's not scoped, thus Database.accdb!module3. is not needed
Run needs a function name, and subpycall is a sub
subpycall needs to be public, which is default, but imo should be specified nonetheless
ac is already the application, so no need to do .Application

Thus:

ac.Run("subpycall")

and:

Public Function subpycall() 'Implicit As Variant
    Call cmdUniverse_Click
End Sub

answered Jun 26, 2020 at 16:48

Erik A

32.8k13 gold badges49 silver badges75 bronze badges

1 Comment

Marco_sbt Over a year ago

Unfortunately this solution doesn't seem to work ac.Run("subpycall") File "<COMObject Access.Application>", line 14, in Run File "C:\Users\PC\AppData\Local\Programs\Python\Python36\lib\site-packages\win32com\client\dynamic.py", line 287, in ApplyTypes result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes) + args) pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2147352562), None)

Collectives™ on Stack Overflow

Calling Access Subroutine from Python

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related