5

I've been beating my head against this for a few days now. I'm trying to use pyodbc to connect to a Microsoft Access DB, and I can't seem to get the connection string right or something. This is what I'm using:

cnxn = pyodbc.connect(r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Path\to\file.accdb')

I keep getting the error:

Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnectW)')

Even though when I run dataSources() I see:

{'MS Access Database': 'Microsoft Access Driver (*.mdb, *.accdb)', 'dBASE Files': 'Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx)', 'Excel Files': 'Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)'}

Is there something I'm severely missing here?

dciliske
  • 157
  • 2
  • 9

2 Answers2

6

This is a 64-bitness problem. I solved it by using 32-bits python and pyodbc.

eowoyn
  • 76
  • 1
  • 2
1

Try adding Provider=MSDASQL. It's deprecated but it seems to work OK:

cnxn = pyodbc.connect(r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};
DBQ=C:\Path\to\file.accdb; Provider=MSDASQL;')
mwolfe02
  • 23,787
  • 9
  • 91
  • 161
  • 1
    Tried it. Doesn't work. Am I really missing something? I'm running Windows 7 Professional with Access 2007 – dciliske Dec 28 '10 at 23:26
  • It worked for me with Access XP (2002) connecting to an .mdb on Windows 7 Professional. Since it is deprecated maybe it doesn't support the new .accdb format. – mwolfe02 Dec 29 '10 at 04:48
  • It also worked for me with Access 2007 connecting to an .accdb on Windows XP Professional. I don't have a Win7 machine with Access 2007 installed, so I can't replicate your environment. – mwolfe02 Dec 29 '10 at 05:16
  • ok, I am really mystified now... Everything I try always gives me the same error string. – dciliske Dec 29 '10 at 10:10
  • @SwimmingCode Do you have some solution to this question you posted without resorting on using 32-bit python and pyodbc? I am running exactly into the same problem. – Jack_of_All_Trades Oct 27 '14 at 21:45
  • Try this: https://www.microsoft.com/en-us/download/details.aspx?id=13255 I failed though because I have installed 32-bit Office. There is no 64bit driver for Access 2007 as it seems, so this may help you. – Pavel Hanpari Jan 05 '16 at 19:54