PYODBC + PYINSTALLER: EXE listing different drivers as compiler (PyCharm), 32 bit Access and 64 bit Python

Question

I have 32bit Access and 64bit Python (on x64 Windows 10).

I successfully run the following code to obtain table (or a query) from my .accdb database and get the desired outcome in my Pycharm.

import pyodbc
import pandas
import os
import sys

sources = pyodbc.dataSources()
dsns = list(sources.keys())
dsns.sort()
sl = []
for dsn in dsns:
   sl.append('%s [%s]' % (dsn, sources[dsn]))
print('\n'.join(sl))

print(pyodbc.drivers())

try:
    currdir = os.path.abspath(__file__)
except NameError:  # We are the main py2exe script, not a module
    import sys
currdir = os.path.abspath(os.path.dirname(sys.argv[0]))
DBfile = os.path.join(currdir, 'UNION.accdb')
cnxn = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=%s;' %DBfile)
sql = "Select * FROM topivot"
df = pandas.read_sql(sql,cnxn)

print(df)

The output is listing all the right drivers.

C:\Users\xxx\AppData\Local\Programs\Python\Python37-32\python.exe C:/Users/xxx/AppData/Local/Programs/Python/Python37-32/pyddb3.py
Excel Files [Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)]
MS Access Database [Microsoft Access Driver (*.mdb, *.accdb)]
UNION [Microsoft Access Driver (*.mdb)]
accdb [Microsoft Access Driver (*.mdb)]
dBASE Files [Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx)]
['Driver da Microsoft para arquivos texto (*.txt; *.csv)', 'Driver do Microsoft Access (*.mdb)', 'Driver do Microsoft dBase (*.dbf)', 'Driver do Microsoft Excel(*.xls)', 'Driver do Microsoft Paradox (*.db )', 'Microsoft Access Driver (*.mdb)', 'Microsoft Access-Treiber (*.mdb)', 'Microsoft dBase Driver (*.dbf)', 'Microsoft dBase-Treiber (*.dbf)', 'Microsoft Excel Driver (*.xls)', 'Microsoft Excel-Treiber (*.xls)', 'Microsoft ODBC for Oracle', 'Microsoft Paradox Driver (*.db )', 'Microsoft Paradox-Treiber (*.db )', 'Microsoft Text Driver (*.txt; *.csv)', 'Microsoft Text-Treiber (*.txt; *.csv)', 'SQL Server', 'ODBC Driver 13 for SQL Server', 'SQL Server Native Client 11.0', 'Microsoft Access Driver (*.mdb, *.accdb)', 'Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)', 'Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx)', 'Microsoft Access Text Driver (*.txt, *.csv)']

However when I run it through Auto-Py-to-Exe (or Pyinstaller, by avoiding max. recursion error and creating EXE from the .spec file), I get the following error:

'SQL Server', 'ODBC Driver 13 for SQL Server', 'PostgreSQL ANSI(x64)',
'PostgreSQL Unicode(x64)', 'Amazon Redshift (x64 I', 'SQL Server Native
 Client 11.0', 'SQL Server Native Client RDA 11.0'] raceback (most recent
 call last): File "pyddb2.py", line 14, in <module> .yodbc.InterfaceError:
 ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not 
 found and no default dri er specified (0) (SQLDriverConnect)') 21444] Failed
 to execute script pyddb2 

This appears to list Windows ODBC 64 drivers. In order to prevent this i tried to revert the target path of the ODBC Data Sources (64-bit) with ODBC Data Sources (32-bit) from

%windir%\system32\odbcad32.exe

to

%windir%\syswow64\odbcad32.exe

but with no avail.

How come that I managed to successfully run the PY file on 32bit Access and 64bit Python, but how to create EXE that would connect. Any ideas?

Answer 1

Could not find the way to connect the right drivers, so I reinstalled Access Engine, Python and Conda. Installed Python 32bit, Access Engine 2016 32bit to match it with Access 32bit. Installed Auto Py To Exe and it worked. The EXE file has now 64MB.