Using an Access database (.mdb) with Python on Ubuntu

Question

I'm trying to use pyodbc to access a .mdb on Ubuntu. I've based my progress so far on this link

I have installed pyodbc, unixodbc, and unixodbc-dev

My code looks like this:

import csv
import pyodbc

MDB = 'URY.mdb'
DRV ='Microsoft Access Driver (*.mdb)'
PWD = 'pass'

conn = pyodbc.connect('DRIVER=%s;DBQ=%s;PWD=%s' % (DRV,MDB,PWD))
curs = conn.cursor()

When I run it, I receive this error message:

Traceback (most recent call last):
  File "mdbscraper.py", line 8, in <module>
    conn = pyodbc.connect('DRIVER=%s;DBQ=%s;PWD=%s' % (DRV,MDB,PWD))
pyodbc.Error: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found,         and no default driver specified (0) (SQLDriverConnect)')

Does anyone have any ideas? Any help would be very much appreciated

Thank you!

I'm not familiar with Linux, but on Windows, this error would mean that you did not set up an ODBC connection in Control Panel -> Administrative Tools -> ODBC Connections. And you'd pass the Data Source Name (DNS) in the connection, not the name of file (that's in the DNS). — HardCode, May 11 '12 at 21:06
I think because I'm using a .mdb it's all local, so I don't need a DNS. Does that sound right? I'm basing it on the example at the top of this page https://code.google.com/p/pyodbc/wiki/ConnectionStrings — mythander889, May 11 '12 at 21:10
I think you still need to install an ODBC driver for MS Access. Easysoft sells one. http://www.easysoft.com/products/data_access/odbc-access-driver/index.html Supposedly mdbtools includes one but it's described as limited. http://mdbtools.sourceforge.net/install/x90.htm — HansUp, May 11 '12 at 21:19
Thank you. I found the necessary driver on their website as a tar. Do you know where I need to put it? — mythander889, May 11 '12 at 21:41
I never tried it. See their getting started guide. http://www.easysoft.com/products/data_access/odbc-access-driver/getting-started.html — HansUp, May 11 '12 at 21:50
I am facing the same error. @mythhander889 how did you fix this? Please share. — fakhir hanif, Jan 13 '16 at 09:47
@fakhirhanif unfortunately, I never got it working. I found a command line utility that did something similar, then wrote a small bash script to dump it out to a csv — mythander889, Jan 14 '16 at 21:02
@mythander889, Now i am able to connect with .mdb file using pyodbc. It is crashing when i fetch records after executing the query. — fakhir hanif, Jan 25 '16 at 09:48

score 5 · Answer 1 · answered Mar 18 '14 at 13:37

5

From what I know this driver "Microsoft Access Driver (*.mdb)" is only available on a Microsoft host, since you are on ubuntu, it won't work.

answered Mar 18 '14 at 13:37

schoash

166
1
6

1

Yes I am with this answer so far. Please share if someone has the solution. – fakhir hanif Jan 13 '16 at 09:48

score -3 · Answer 2 · edited Sep 04 '12 at 16:06

import pyodbc 
DBfile = '/data/MSAccess/Music_Library.mdb'
conn = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb)};DBQ='+DBfile)
cursor = conn.cursor()
SQL = 'SELECT Artist, AlbumName FROM RecordCollection ORDER BY Year;'

for row in cursor.execute(SQL): # cursors are iterable
print row.Artist, row.AlbumName

cursor.close()
conn.close()

There is The Official Example .. of use ...

Using an Access database (.mdb) with Python on Ubuntu

2 Answers2

Linked