PYODBC InterfaceError- Data source name not found

Question

I am trying to connect Python to MS Access Database using pyodbc but every time I get the following error:

pyodbc.InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

And this what I have written to connect python to MS Access:

import pyodbc

conn = pyodbc.connect(r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=D:\PILOT_DATA.accdb;')
cursor = conn.cursor()
cursor.execute('select * from p_inventor')

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

According to the error, it doesn't find the Data source name and so I changed the 'DRIVER' to 'DSN'

import pyodbc

conn = pyodbc.connect(r'DSN={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=D:\PILOT_DATA.accdb;')
cursor = conn.cursor()
cursor.execute('select * from p_inventor')

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

But it doesn't help. I get the following error:

pyodbc.Error: ('IM010', '[IM010] [Microsoft][ODBC Driver Manager] Data source name too long (0) (SQLDriverConnect)')

Other workaround I have tried is to use both python 32 and 64 bit

Here goes the version details:

• Python 3.7.4 64 bit
• pip 19.2.3
• pyodbc-4.0.27
• Office365 16

Would be really helpful to know what else I can do to connect Python to ACCESS database. Thanks in Advance!

Answer 1

I have solved this issue by installing the Access Database Engine. In order to do that, I had to unistall the office365 program-> install access database engine-> re-install office365. And then the code runs perfectly!

Answer 2

According to the pyodbc docs you need to set-up an ODBC and you can check it like so (as @Parfait said) ;

import pyodbc
[x for x in pyodbc.drivers() if x.startswith('Microsoft Access Driver')]

Answer 3

If Office 365 is installed as "Click-to-Run" (C2R) then some Office components are stored in an "isolated environment" that is not visible to non-Office applications. The Access Database Engine is one of those components.

In that case the solution for using the Access Database Engine from external (non-Office) applications is to download and install the Access Database Engine Redistributable package as explained in the Microsoft Docs article:

Can't use the Access ODBC driver or OLEDB provider outside Office Click-to-Run applications

Answer 4

Installing the Access Driver on Microsoft website worked for me.

Before installing the driver I also got an InterfaceError and running

 import pyodbc
 [x for x in pyodbc.drivers() if x.startswith("Microsoft Access Driver")]

yielded an empty list [].

After installing the driver (x64 version), it appears in the list from the code above and calling conn.cursor() doesn't yield the error anymore:

import pyodbc
[x for x in pyodbc.drivers() if x.startswith("Microsoft Access Driver")]
Out[1]: ['Microsoft Access Driver (*.mdb, *.accdb)']