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

Question

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

Answer 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:

1. Omit the first [ODBC] line
2. Put curly braces around all values containing spaces
3. 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.

Answer 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:

1. 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.

2. 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.

Answer 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.