0

I trying to open MS Access Database. I have Python 3.10.10. After the first time I encountered this issue, I tried a few recommended fixes and keep getting the following error

ModuleNotFoundError: No module named 'pyobdc'.

I see the module pyodbc in the listing by executing ‘pip list’ in the command prompt and by running a Python script that checks for installed packages both outputs confirm that pyodbc is installed.

Python script output search for modules installed.py:
== RESTART: C:\Code\GUI in Python\How to check which modules are installed.py ==
['altgraph', 'auto-py-to-exe', 'bottle', 'bottle-websocket', 'cffi', 'Eel', 'future', 'gevent', 'gevent-websocket', 'greenlet', 'pefile', 'pycparser', 'pyinstaller', 'pyinstaller-hooks-contrib', 'pyodbc', 'pyparsing', 'pywin32-ctypes', 'whichcraft', 'zope.event', 'zope.interface']

CMD output:

C:\Users\georg>pip list
pyodbc                    4.0.35

But when I try to connect to my Access Database I am getting that error.
Which directory does the pyodbc needs to be located in?

I uninstalled pyodbc and all versions of Python (I had 3.7, 3.8) and reinstalled Python 3.10.10, and using pip installed pyodbc. Ran the connecting script and still receiveing same error.

I am new to this. I want to start all over. This time I will follow your advice and provide feedback. One thing I found is that MS Access *.accdb has to be the same bit as Python.
(From mkleehammer)."Therefore, if you already have Microsoft Office it is highly recommended that you use a Python environment that matches the "bitness" of the Office install. https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-Microsoft-Access."
Now I have both at 32-bit. The following was written in 32 bit Python:

import pyodbc
#Making the connection
conn = pyodbc.connect (r' Driver = {Microsoft Access Driver(*mdb, *.accdb)}
DBQ = "C:\Code\GUI in Python\Church membership database development\Test 1 for importing to Python.accdb" ')

Traceback:

============================ RESTART: C:/Code/aa.py ===================
Traceback (most recent call last):
  File "C:/Code/aa.py", line 3, in <module>
    conn = pyodbc.connect (r' Driver = {Microsoft Access Driver(*mdb, *.accdb)}; DBQ = "C:\Code\GUI in Python\Church membership database development\Test 1 for importing to Python.accdb" ')
pyodbc.InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')
Debug: KeyError: 'pyodbc'
imxitiz
  • 3,920
  • 3
  • 9
  • 33
Jerzy
  • 1
  • 1
  • Try instead running `python -m pip list` to verify that the normal Python that get started has that module installed, and then use the same `python` run your script. Do include the relevant `Traceback` output. – metatoaster Feb 21 '23 at 03:53
  • I have tried the python -m pip list and the listing shows that pyodbc exists, and then I ran the python script with the same error no pydbc module found. Thew following is the output. ====== RESTART: C:\Code\Open MS Access database\Open Ms Access database.py ===== Traceback (most recent call last): File "C:\Code\Open MS Access database\Open Ms Access database.py", line 21, in import pyobdc as pyo ModuleNotFoundError: No module named 'pyobdc' – Jerzy Feb 22 '23 at 12:39
  • The `RESTART` is very suspicious. You will need to verify that the restart is using the same Python environment. Also, without the code it makes it impossible for anyone else to reproduce the specific issue you are facing, which means nobody else will be able to tell you exactly what the issue actually is. One thing I would attempt to do is to either use a debugger or edit the affected file to insert a `import sys;print(sys.executable);print(sys.path)` to verify that the expected Python executable is being used and correct environment paths are actually present. – metatoaster Feb 22 '23 at 22:34
  • metatoaster, do I have to insert: import sys;print(sys.executable);print(sys.path) into that problematic file or can I run that line of code in a single line? And if so what should I see for an output? – Jerzy Mar 13 '23 at 02:28
  • Insert those lines above the `import pyodbc` statement, and also run them separately to see if they are the same. The `sys.path` should contain a directory that contains the `pyodbc` package if `pyodbc` is imported, otherwise it can't be imported. – metatoaster Mar 13 '23 at 08:53
  • I'm using Python IDLE Shell 3.10.10. I ran import sys;print(sys.executable);print(sys.path) on it's own line nothing else and received: C:\Users\georg\AppData\Local\Programs\Python\Python310\pythonw.exe and other that point to Python310 with other subdirectories. I also ran the import pyodbc with the line mentioned above and received no error. When I run this: import pyodbc msa_drivers = [x for x in pyodbc.drivers() if 'Access' in x.upper()] print(f'MS-Access Drivers : {msa_drivers}'). I get an empty bracket, MS-Access Drivers : []. – Jerzy Mar 18 '23 at 02:47
  • `x.upper()` will result in all UPPERCASE, and `Access` is not all uppercase. Just print out the whole list and see what drivers are actually available, though that alone shouldn't case `import pyodbc` to fail. You will need to attach a Python debugger to whatever Python code you are running and step through and into the running Python program line by line. – metatoaster Mar 18 '23 at 06:39
  • c:\>python -V Python 3.10.10 The python -V returns the correct version and that being asked at the root directory that tells me that the Windows environment for Python is set up correctly. – Jerzy Mar 23 '23 at 03:40
  • Also, checking the directory for pydbc in \Directory of C:\Users\georg\AppData\Local\Programs\Python\Python310 02/20/2023 10:18 PM Lib 02/20/2023 10:18 PM libs 02/07/2023 07:26 PM 32,768 LICENSE.txt 02/07/2023 07:27 PM 1,301,651 NEWS.txt 02/19/2023 11:04 PM 13,620 pyodbc.pyi (is this the correct file and location?) – Jerzy Mar 23 '23 at 03:44
  • There should be a `C:\Users\georg\AppData\Local\Programs\Python\Python310\site-packages\pyodbc` directory - `pyodbc.pyi`is a stub file. As I said before, you will need to attach a Python debugger if you fail to replicate the issue just by running python and `import pyodbc` does not raise the `ImportError`. There is nothing you have provided so far that indicates to me that a problem that I can spot for you, the problem may in fact only exist on your machine and you will need to trace the issue with a debugger. Especially given that a complete `Traceback` output was not provided. – metatoaster Mar 23 '23 at 04:19
  • I read up on this problematic pyodbc, many people have problems with connecting. First, the *.accdb file must be at the same bit as Python. My MS Access is 32 bit and Python 64. I got 32 bit Python, rewrote the following to connect: import pyodbc # No error importing #Making the connection conn = pyodbc.connect(r'Driver={Microsoft Access Driver(*.mdb, *.accdb)};DBQ=C:\Code\Test 1 for importing to Python.accdb;') # Error:InterfaceError: I will provide the error after you acknowledge this comment since there are only 56 characters left. – Jerzy Mar 26 '23 at 19:50
  • Please just edit the question (could do it as an update, append the new information below what you got), it is after all what you are asking and the formatting will make it all the easier to read, and therefore understand. – metatoaster Mar 27 '23 at 21:45
  • You might want to validate that what you expected is available, see [this answer](https://stackoverflow.com/a/56983227/), follow that precisely. I will also note that `(r' Driver = {Microsoft Access Driver(*mdb, *.accdb)}` is a typo as there is no ending quote and there are weird spaces and missing characters (e.g. it's `*.mdb`) that might affect how this works. This [thread](https://stackoverflow.com/questions/58571740/why-am-i-getting-data-source-name-not-found-and-no-default-driver-specified-an) may also be very relevant. – metatoaster Mar 29 '23 at 07:36

0 Answers0