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?
