Pyodbc error Data source name not found and no default driver specified paradox

python windows python-2.7 pyodbc

111,308

Solution 1

Two thoughts on what to check:

1) Your connection string is wrong. There's a way to get a known good connection string directly from the ODBC Administrator program (taken from http://www.visokio.com/kb/db/dsn-less-odbc). These instructions assume you're using an MDB, but the same process will work for a paradox file

On a typical client PC, open Control Panel -> Administrative Tools -> Data Sources.
Select the File DSN tab and click Add.
Select the appropriate driver (e.g. "Microsoft Access Driver (*.mdb)") and click Next
Click Browse and choose where you want to save the .dsn file (this is a temporary file you are going to delete later).
Click Next then Finish.
You will be shown the vendor-specific ODBC setup dialog. For example, with Microsoft Access, you might only need to click Select and browse to an existing .mdb file before clicking OK.
Browse to the location of the .dsn file and open using Notepad.

In the DSN file you might see something similar to:

[ODBC]
DRIVER=Microsoft Access Driver (*.mdb)
UID=admin
UserCommitSync=Yes
Threads=3
SafeTransactions=0
PageTimeout=5
MaxScanRows=8
MaxBufferSize=2048
FIL=MS Access
DriverId=25
DefaultDir=C:\
DBQ=C:\db1.mdb

To convert the above to the full connection strring:

Omit the first [ODBC] line
Put curly braces around all values containing spaces
Put all name=value pairs on one line, separated by semicolons.

This gives you the full connection string. In this example, the string becomes:

DRIVER={Microsoft Access Driver (*.mdb)};UID=admin;UserCommitSync=Yes;Threads=3;SafeTransactions=0;PageTimeout=5;axScanRows=8;MaxBufferSize=2048;FIL={MS Access};DriverId=25;DefaultDir=C:\;DBQ=C:\db1.mdb

2) 32/64 bit mismatch. I've had troubles when mixing 32-bit python with 64-bit drivers, or vice-versa. You may want to check your Python interpreter and database driver line up.

Solution 2

You need a Microsoft Office version compatible with your Python installation, i.e. both of them must be either 32 bit or 64 bit. From the pyodbc documentation:

There are actually two (2) different Access ODBC drivers from Microsoft:

Microsoft Access Driver (*.mdb) - This is the older 32-bit "Jet" ODBC driver. It is included as a standard part of a Windows install. It only works with .mdb (not .accdb) files. It is also officially deprecated.

Microsoft Access Driver (*.mdb, *.accdb) - This is the newer "ACE" ODBC driver. It is not included with Windows, but it is normally included as part of a Microsoft Office install. It is also available as a free stand-alone "redistributable" installer for machines without Microsoft Office. There are separate 64-bit and 32-bit versions of the "ACE" Access Database Engine (and drivers), and normally one has either the 64-bit version or the 32-bit version installed. (It is possible to force both versions to exist on the same machine but it is not recommended as it can "break" Office installations. 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.)

The easiest way to check if one of the Microsoft Access ODBC drivers is available to your Python environment (on Windows) is to do
>>> import pyodbc
>>> [x for x in pyodbc.drivers() if x.startswith('Microsoft Access Driver')]
If you see an empty list then you are running 64-bit Python and you need to install the 64-bit version of the "ACE" driver. If you only see ['Microsoft Access Driver (*.mdb)'] and you need to work with an .accdb file then you need to install the 32-bit version of the "ACE" driver.

Solution 3

Thanks for the question, I had a similar problem, and this question and the answers helped lead me to what I needed. The problem for me ended up being a mismatch between 64-bit Python and 32-bit ODBC Driver on Windows 10 (as Chad Kennedy suggested). I'm running a fully updated Fall Creator's Edition, and had Microsoft Office Pro 2016 installed. The MS Office installer still defaults to a 32-bit installation (don't get me started...) -- it doesn't ask about this at install time, so imagine my surprise when I discovered I was running 32-bit Office. Because of this, it installs the 32-bit ODBC driver for MS Access. There is a tiny unnoticeable link you can click in the MS Office installer dialog to force the 64-bit install.

A 64-bit Python installation won't work with the 32-bit Microsoft Access ODBC driver, and Microsoft won't let you install the 64-bit ODBC driver if you have 32-bit MS Office installed on the machine.

The fix was to UNINSTALL MS Office, and re-install it by using that tiny link on the install dialog to tell it to install as 64-bit. Don't worry, it remembers all of your recent files and settings, and email accounts in Outlook. Once that was done, I had the 64-bit ODBC driver, and my Python code connected to the database with no further problems.

111,308

Marcello B.

I am a self-taught programmer. I had always had an interest in computers since I was a toddler. When I was about 6 I "helped" my grandfather build me my first computer. When I got a bit older (around 10) my grandfather went to a local scrap computer parts store and purchased a bunch of parts. For the next summer, he and I would build computers and diagnosis issues with them. About a year later I began learning how to code from a gentleman at the age of 11. I started out making simple graphics with Haskell on his website https://code.world/, soon we made simple games. Once I learned the basics of OOP, I began learning Python. I made a few widgets for my self, but never really developed with Python professionally. At the age of 13, I saw an issue with the way trap tournaments were run. I had decided to create my first public application On-Deck Shooting Apps. It was used at a few small clubs here in Colorado. About 6 months into the project, my application was seen by the owner of a software company for trapshooting. He offered me a job. Since then I have been working with many different technologies. On a day-to-day basis, I work with C# (WinForms/WPF/Xamarin), C++, PHP, AWS, and jQuery. Once I graduated High School, I started taking classes at a local community college. I had decided that I wanted to start working towards a business degree and a computer science degree. However, they did not allow their students to double major. Therefore I decided to go for certificates, and along the way earn my Associates of General Studies. If being in college and working full time was not enough, I still like to challenge myself by learning new technologies. Right now I am taking a course on ASP.NET core, and another on Xamarin via Udemy. To help sharpen my skills. I also like to develop my own hardware, I fill up my spare time by making Arduino/Raspberry pi devices. I prototype on a breadboard then design and order PCBs and assemble them myself. Some are IoT devices others come into play at work. I am just an all-around nerd that loves to learn and experiment with anything electronic I can get my hands on.

Updated on March 11, 2021

Comments

Marcello B. about 3 years
I am attempting to use pyobdc to read data from a paradox database, and I keep getting the following error when attempting to connect to the database:
```
pyodbc.Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')
```
I have tried to create new DNS links for the database but it has not helped what so ever.

My system links are as follows:

My code is:
```
import os
import sys
import time
import pyodbc

LOCATION = "c:\Users\Marcello\Desktop\DATA\ScorMonitor.db"

cnxn = pyodbc.connect(r"Driver={{Microsoft Paradox Driver (*.db )}};Fil=Paradox 5.X;DefaultDir={0};Dbq={0}; CollatingSequence=ASCII;")
cursor = cnxn.cursor()
cursor.execute("select last, first from test")
row = cursor.fetchone()
print row
```
- idjaw over 8 years
  
  I don't know if you have seen this already but this post has a connection string for the paradox driver. Maybe you can try this to see if it helps resolve? It looks similar to yours, but some differences exist. stackoverflow.com/questions/13651087/…
- Marcello B. over 8 years
  
  Yes I followed all the steps that were suggested on that post and it did not solve the problem
- Gord Thompson over 8 years
  
  Does your machine have the Borland Database Engine (or more modern compatible equivalent) installed?
- Marcello B. over 8 years
  
  @GordThompson no I do not
- Marcello B. over 8 years
  
  @GordThompson I do have the BDE Administrator
- rll over 8 years
  
  Assuming that your code is actually complete (the example is missing the format of the connection string to include LOCATION), try using double backslash on the path. I had this issue in windows systems, although I was using C# not python.
- Marcello B. over 8 years
  
  @rll the code posted above is my full code
- rll over 8 years
  
  then you should have: "Driver={{Microsoft Paradox Driver (*.db )}};Fil=Paradox 5.X;DefaultDir={0};Dbq={0}; CollatingSequence=ASCII;".format(LOCATION) Otherwise your {0} is not replaced
- Avnish alok over 4 years
  
  Possible duplicate of PYODBC--Data source name not found and no default driver specified
Marcello B. over 8 years

Thank you for such a through answer. But I was wondering where I can find the DNS like that. I am very new to having to use a driver to access as database.
Chad Kennedy over 8 years

You don't have to find the DSN file. The process I described will generate a DSN file. From there you can get the full connection string, as described. The only thing you'll need to do is pick the right driver and select the right file, both of which it seems you already know.
Marcello B. over 8 years

Thank you that makes more sense now. Applying what I have learned from your post gives me this error: KeyError: 'Microsoft Paradox Driver (*' I am terribly sorry but this is a very new area for me.
Chad Kennedy over 8 years

Hmm.. Did you remember to add the curly braces? Like this: DRIVER={Microsoft Access Driver (*.mdb)} If it's not that, you may want to start a new question, giving the full connection string in the question, as well as whatever code before and after is necessary for context.
Marcello B. over 8 years

Will you take a quick look before I post a new question? pastebin.com/Y6HDwvda
Chad Kennedy over 8 years

Ah, I see. You should remove the .format(LOCATION) part. That will fix your error. However, I notice you had set LOCATION to a different file from what you have listed under the DBQ tag in the connection string. Make sure you know which file contains the database you care about.
Marcello B. over 8 years

Fixed! Thank you so much!
Marcello B. over 8 years

Once I ran it a second time it broke with the same error code
Alberto Perez over 5 years

Even I had all in 64 bits but it does not work, I have to change all to 32 bits to make it work
bsa almost 5 years

Thanks. The 32/64-bit mismatch was my issue.
eksortso almost 4 years

The question was about reading a Paradox file, not an MS Access database. But thank you for your advice, because it helped me out.
user__42 over 3 years

simply listing all of pyodbc.drivers() hinted me to use driver version 17 instead of 13