Python Forum
Using VBA to Call a Python script causes error in pyodbc connector - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Using VBA to Call a Python script causes error in pyodbc connector (/thread-19025.html)

Using VBA to Call a Python script causes error in pyodbc connector - pcarra - Jun-10-2019

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!

RE: Using VBA to Call a Python script causes error in pyodbc connector - pcarra - Jun-11-2019

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!