Python Forum

Full Version: Using VBA to Call a Python script causes error in pyodbc connector
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Hello, I am using vba to call a python script that gets information from a Jira API and uses pyodbc to update an Access database. Everything works fine if I open the python script and run it from an interpreter, but if i use vba to call the script I get the below error.

Error:
Traceback (most recent call last): File "C:\Users\PCarra\Desktop\CktOpsDb\Scripts\updateDb.py", line 7, in <module> conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=\\**Omitted on purpose**\Backend\Inventory_be.accdb;')pyodbc.InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')
Python code below:

from collections import Counter
from jira import JIRA
from pprint import pprint
import requests, json, urllib3, os, pyodbc, usaddress

#Connection string for database
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=\\**omitted on 
                      purpose**\Backend\Inventory_be.accdb;')
cursor = conn.cursor()
...
...
cursor.execute('''
                UPDATE CircuitInfoTable
                SET CircuitID = ?, Bandwidth = ?, Region = ?, HandoffALocAddress = ?, HandoffALocCity = ?, HandoffALocST = ?, 
                Carrier = ?, HandoffZLocAddress = ?, HandoffZLocCity = ?, HandoffZLocST = ?, SegmentID = ?, Legacy = ? WHERE 
                LatestJiraTicket = ? 
                ''', params)

conn.commit()
VBA call to script:

Output:
Dim x As Variant Dim strProgramName As String strProgramName = CurrentProject.Path & "\Scripts\updateDb.py" Shell Environ$("COMSPEC") & " /k" & strProgramName, vbNormalFocus
Any ideas on what may be causing the above error since it works through an interpreter but not after I call the script using VBA?

Thanks for the help!
I found my problem from within the shell called from vba I needed to specify a 32 bit version of python to execute the script. Also I created a System DNS connector on my system. Not a python code problem. Sorry!