How to connect pyodbc to an Access (.mdb) Database file

Question

Here's what I've tried:

-Find Vista's ODBC Data Source Manager* through search,

-Add a new File Data Source*, selecting Driver for Microsoft Access (*.mdb), and selecting my mdb file of interest,

-import pyodbc from python shell and try:

pyodbc.connect("DSN=<that Data Source I just created>")

I get the following error message (Portuguese**):

Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Nome da fonte de dados n\xe3o encontrado e nenhum driver padr\xe3o especificado (0) (SQLDriverConnectW)') 

Which translates as "Data source name not found and no standard driver specified".

What am I doing wrong? How to get it right? Also, I searched the web for documentation but found nothing worth much, could anyone recommend any documentation?

*Names may not be completely accurate because my Windows is in Portuguese.

**No, Portuguese doesn't have '3' and '\' as letters, these are misprinted special characters

Answer 1

DSN= is only used for a system or user DSN.

For a DSN File, you need to use FILEDSN=c:\myDsnFile.dsn

http://www.connectionstrings.com/ is your best friend.

Answer 2

I use odbc module (included in ActiveState Python), but tested pyodbc and for me works:

#db = odbc.odbc('northwind')
#db = odbc.odbc('Driver={Microsoft Access Driver (*.mdb)};Dbq=Nwind.mdb;Uid=;Pwd=;')
#db = pyodbc.connect('Driver={Microsoft Access Driver (*.mdb)};Dbq=Nwind.mdb;Uid=;Pwd=;')
db = pyodbc.connect('DSN=northwind')

Of course commented connections works too.

I configured nothwind as user DSN so you probably will have to configure your ODBC database connection as User DSN or System DSN, or without configuring in ODBC Administrator you can use ConnectString where you can point at your .mdb file.