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 had a similar problem with pyodbc although not with Access but a different ODBC driver.

This is what helped me. http://robertoschiabel.wordpress.com/2008/02/28/windows-x64-32bit-odbc-vs-64bit-odbc/ (here is the appropriate KB article in case this URL goes away. http://support.microsoft.com/kb/942976/en-us )

Our previous server hardware died and we had to quickly redeploy on a 64 bit OS because that is all we had that was available. Using the normal ODBC admin tool I added the appropriately named DSN but it still claimed it wasn't found. Only when running the special 32-bit version of the ODBC admin, was I able to define a DSN that my script using pyodbc could find.