0

I'm getting an exception right from the start of trying to assign the driver. I've generated the DNS using 32 bit executable Odbcad32.exe in the SysWoW64 folder. I believe I created the driver string correctly (wrapped parentheses for spaces, semi colons at the ends, and dropped the first row), but no matter the tweaks I try I get this exception on the assignment of pyodbc.connect. The .mdb file is local obviously.

Code:

import pyodbc

con = pyodbc.connect('DRIVER=(Driver do Microsoft Access (*.mdb));UID=admin;UserCommitSync=Yes;Threads=3;SafeTransactions=0;PageTimeout=5;MaxScanRows=8;MaxBufferSize=2048;FIL=(MS Access);DriverId=25;DefaultDir=C:\\Users\Mac2\Desktop\TLC_Program_Release\Scripts;DBQ=C:\\Users\Mac2\Desktop\TLC_Program_Release\Scripts\SetupSheets.mdb')
cursor = con.cursor()

cursor.execute('SELECT * FROM ProgramData')

for row in cursor.fetchall():
   print(row)

Exception:

Exception has occurred: pyodbc.InterfaceError
('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Chris Macaluso
  • 1,372
  • 2
  • 14
  • 33
  • 1
    Try enclosing the driver name in curly brackets `{}` instead of round brackets `()`: `DRIVER={Driver do Microsoft Access (*.mdb)}` – Gord Thompson Dec 22 '18 at 00:37
  • good suggestion, I hadn't tried that. Same thing tho with `DRIVER={Driver do Microsoft Access (*.mdb)}`. Reading a bit about similar issues people are having, do you think this could be an issue with my python environment being 64 bit and the DNS driver / .mdb file being 32 bit? – Chris Macaluso Dec 22 '18 at 14:59
  • 1
    Definitely. The older "Jet" drivers "`(*.mdb)`" are only available to 32-bit applications. Assuming that you don't already have 32-bit Office installed you can install the 64-bit version of the newer "ACE" driver "`(*.mdb, *.accdb)`" and use that. Use the list returned by `pyodbc.drivers()` to verify the drivers that are visible to your Python app. – Gord Thompson Dec 22 '18 at 15:23
  • @GordThompson Still having problems, I uninstalled my 32 bit Office and installed 'Microsoft Access Database Engine 2010 Redistributable' which is 64 bit. This did now populate the Odbcad32.exe file DNS tab (in the 32 bit folder, this is confusing why they're backwards!) with `.mdb` and `.accdb` options. I recreated the DNS file then and adjusted the formatting as I described above. I'm getting the same exact error though. – Chris Macaluso Dec 22 '18 at 22:50
  • @GordThompson Running the `pyodbc.drivers()` command as you mentioned prints: `['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)']` .mdb is included, so I'm not sure why it's not working now? – Chris Macaluso Dec 22 '18 at 22:53
  • 1
    Are you using `DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}` ...? – Gord Thompson Dec 22 '18 at 23:00
  • That was it, sorry I forgot I went back to trying other things and saved it that was. It should be in the format you specified. Thank you for the help @GordThompson – Chris Macaluso Dec 24 '18 at 13:16

0 Answers0