Unable to Open access database using Python

Question

I am unable to open an access database using python. Below is the code and the error message. I even tried going to control panel checking where the path of the odbc file is pointing to. It shows up in the correct path. %windir%\syswow64\odbcad32.exe not sure how to avoid this message, The below code also shows the drivers that are available.

import pyodbc


def show_odbc_sources():
    sl = []
    source = odbc.SQLDataSources(odbc.SQL_FETCH_FIRST)
    while source:
        dsn, driver = source
        sl.append('%s [%s]' % (dsn, driver))
        source = odbc.SQLDataSources(odbc.SQL_FETCH_NEXT)
    sl.sort()
    print('\n'.join(sl))


if __name__ == '__main__':
    show_odbc_sources()

conn = pyodbc.connect(r'driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\Users\\username\\Desktop\\E CX DB.accdb;')
cursor = conn.cursor()

Errors That i get:

Excel Files [Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)]
MS Access Database [Microsoft Access Driver (*.mdb, *.accdb)]
Sample Amazon Redshift DSN [Amazon Redshift (x64)]




---------------------------------------------------------------------------
InterfaceError                            Traceback (most recent call last)
<ipython-input-12-816aa9101ab7> in <module>()
     16         show_odbc_sources()
     17 
---> 18 conn = pyodbc.connect(r'driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\Users\\username\\Desktop\\E CX DB.accdb;')
     19 cursor = conn.cursor()

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

Does Access require any sort of Access specific ODBC driver? — Joshua Schlichting, Aug 27 '18 at 15:18
@JoshuaSchlichting That is the driver i have which is 32 bit version. Also i checked the solutions provided by others it is the same. https://stackoverflow.com/questions/28708772/how-to-connect-ms-access-to-python-using-pyodbc. Here is one solution that was shared earlier. — Ren Lyke, Aug 27 '18 at 17:05
Check the list returned by `pyodbc.drivers()` to see if the Access ODBC driver is in there. — Gord Thompson, Aug 27 '18 at 23:25
@GordThompson These are the list of drivers Excel Files [Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)] MS Access Database [Microsoft Access Driver (*.mdb, *.accdb)] Sample Amazon Redshift DSN [Amazon Redshift (x64)] — Ren Lyke, Aug 28 '18 at 12:22
@RenLyke - That is not the form of the list that pyodbc returns. I'm asking what *pyodbc* sees as the list of available drivers, not what your current code returns. — Gord Thompson, Aug 28 '18 at 13:00
@GordThompson when i used `pyodbc.drivers()`. i get this output ['SQL Server', 'SQL Server Native Client 10.0', 'Amazon Redshift (x64)', 'MySQL ODBC 5.3 ANSI Driver', 'MySQL ODBC 5.3 Unicode Driver', 'PostgreSQL ANSI(x64)', 'PostgreSQL Unicode(x64)', 'Oracle in OraClient12Home1', 'SQL Server Native Client 11.0'] — Ren Lyke, Aug 28 '18 at 17:24

score 0 · Accepted Answer · answered Aug 28 '18 at 19:12

That is the driver i have which is 32 bit version.

It appears that you are running a 64-bit version of Python, so pyodbc cannot see the 32-bit version of the Access ODBC driver. You'll either need to switch to a 32-bit version of Python or switch to the 64-bit version of the Access ODBC driver.

Unable to Open access database using Python

1 Answers1