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
– Joshua Schlichting, Commented Aug 27, 2018 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. stackoverflow.com/questions/28708772/…. Here is one solution that was shared earlier. — Ren Lyke
– Ren Lyke, Commented Aug 27, 2018 at 17:05
Check the list returned by pyodbc.drivers() to see if the Access ODBC driver is in there. — Gord Thompson
– Gord Thompson, Commented Aug 27, 2018 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
– Ren Lyke, Commented Aug 28, 2018 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
– Gord Thompson, Commented Aug 28, 2018 at 13:00

Gord Thompson · Accepted Answer · 2018-08-28 19:12:11Z

0

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.

answered Aug 28, 2018 at 19:12

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Unable to Open access database using Python

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related