search

mkleehammer / pyodbc

Python ODBC bridge
https://github.com/mkleehammer/pyodbc/wiki
MIT No Attribution
2.94k stars 562 forks source link

InterfaceError [Microsoft] [ODBC Driver Manager] Data source name not found and no default driver specified #971

Closed mookjais closed 2 years ago

mookjais commented 3 years ago

Observed behavior: When I connect the database in VS Code, the connection is made & there are no errors, but when I compile the code into an .exe file w/ pyinstaller, I get the following error.

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

Here is the connection string: conn_str = (r"DRIVER=Microsoft Access Driver (*.mdb, *.accdb); DBQ="networkdrive\myDb.accdb;") conn = pyodbc.connect(conn_str) crsr = conn.cursor()

Expected Behavior: Connection to the database is made successfully

v-chojas commented 3 years ago

Could you post an ODBC trace?

mookjais commented 3 years ago

` HWND 0x0000000000000000

                          WCHAR *             0x00007FFCAACD21A0 [      -3] "******\ 0"

                          SWORD                       -3

                          WCHAR *             0x00007FFCAACD21A0

                          SWORD                       -3

                          SWORD *             0x0000000000000000

                          UWORD                        0 <SQL_DRIVER_NOPROMPT>

test 2858-2e5c EXIT SQLDriverConnectW with return code -1 (SQL_ERROR)

                          HDBC                0x0000018B987E7FA0

                          HWND                0x0000000000000000

                          WCHAR *             0x00007FFCAACD21A0 [      -3] "******\ 0"

                          SWORD                       -3

                          WCHAR *             0x00007FFCAACD21A0

                          SWORD                       -3

                          SWORD *             0x0000000000000000

                          UWORD                        0 <SQL_DRIVER_NOPROMPT>

                          DIAG [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0)

test 2858-2e5c ENTER SQLGetDiagRecW

                          SQLSMALLINT                  2 <SQL_HANDLE_DBC>

                          SQLHANDLE           0x0000018B987E7FA0

                          SQLSMALLINT                  1

                          SQLWCHAR *          0x0000002B9F5187C0

                          SQLINTEGER *        0x0000002B9F518798

                          SQLWCHAR *          0x0000018B9A9291B0

                          SQLSMALLINT               1023

                          SQLSMALLINT *       0x0000002B9F518790

test 2858-2e5c EXIT SQLGetDiagRecW with return code 0 (SQL_SUCCESS)

                          SQLSMALLINT                  2 <SQL_HANDLE_DBC>

                          SQLHANDLE           0x0000018B987E7FA0

                          SQLSMALLINT                  1

                          SQLWCHAR *          0x0000002B9F5187C0 [       5] "IM002"

                          SQLINTEGER *        0x0000002B9F518798 (0)

                          SQLWCHAR *          0x0000018B9A9291B0 [      91] "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

                          SQLSMALLINT               1023

                          SQLSMALLINT *       0x0000002B9F518790 (91)

test 2858-2e5c ENTER SQLGetDiagRecW

                          SQLSMALLINT                  2 <SQL_HANDLE_DBC>

                          SQLHANDLE           0x0000018B987E7FA0

                          SQLSMALLINT                  2

                          SQLWCHAR *          0x0000002B9F5187C0

                          SQLINTEGER *        0x0000002B9F518798

                          SQLWCHAR *          0x0000018B9A9291B0

                          SQLSMALLINT               1023

                          SQLSMALLINT *       0x0000002B9F518790

test 2858-2e5c EXIT SQLGetDiagRecW with return code 100 (SQL_NO_DATA_FOUND)

                          SQLSMALLINT                  2 <SQL_HANDLE_DBC>

                          SQLHANDLE           0x0000018B987E7FA0

                          SQLSMALLINT                  2

                          SQLWCHAR *          0x0000002B9F5187C0

                          SQLINTEGER *        0x0000002B9F518798

                          SQLWCHAR *          0x0000018B9A9291B0

                          SQLSMALLINT               1023

                          SQLSMALLINT *       0x0000002B9F518790

test 2858-2e5c ENTER SQLFreeHandle

                          SQLSMALLINT                  2 <SQL_HANDLE_DBC>

                          SQLHANDLE           0x0000018B987E7FA0

test 2858-2e5c EXIT SQLFreeHandle with return code 0 (SQL_SUCCESS)

                          SQLSMALLINT                  2 <SQL_HANDLE_DBC>

                          SQLHANDLE           0x0000018B987E7FA0`
gordthompson commented 3 years ago

Try creating an executable using the same procedure as your example above except that the python code to run is

import pyodbc

print(pyodbc.drivers())

to see what drivers are available in that context.

mookjais commented 3 years ago

Thanks, here are the drivers available in that context.

SnipImage.JPG

It looks like wherever they are pulling from, the Access driver isn't available. I wonder if i can specify where to get the driver from.

mookjais commented 3 years ago

I see the issue, it's pulling the 64-bit drivers instead of the 32-bit drivers. I'm not sure how to change that.

gordthompson commented 3 years ago

Does this help?

https://stackoverflow.com/q/7155866/2144390

mookjais commented 3 years ago

I've been working on your solution all morning & it has been helpful. I found:

-The 64 bit version of python I had installed was causing issues, so I uninstalled it -I uninstalled & reinstalled pyodbc & pyinstaller -I'm still getting the interface error but its because some built-in python code is looking for site-packages in the old 64 bit file path

I'm trying to figure out how it is still trying to pull from the old path even through I set the Windows environment variable

mookjais commented 3 years ago

I'm a little closer. When I call:

python -m site

I get correct site-packages for sys.path, but USER_BASE & USER_SITE are pointing to the old 64 bit installation (which was installed @ the user level). I don't know how to have Python to stop look @ the user installation.

v-chojas commented 3 years ago

It seems your issue may be going beyond the scope of pyODBC. Make sure the bitness of binaries is consistent.

mookjais commented 3 years ago

@v-chojas yeah, I think so. I'm going to reinstall Python & I'll post the verdict.

mookjais commented 2 years ago

I uninstalled Python & reinstalled & this fixed the issue

MehmetUzel commented 1 year ago

Try creating an executable using the same procedure as your example above except that the python code to run is

import pyodbc

print(pyodbc.drivers())

This saved my 4 days thank you very much