9

I am on Win7 x64, using Python 2.7.1 x64. I am porting an application I created in VC++ to Python for educational purpouses.
The original application has no problem connecting to the MS Access 2007 format DB file by using the following connection string:
OleDbConnection^ conn = gcnew OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=|DataDirectory|DB.accdb");
Now, when I try to connect to the same DB file (put in C:\ this time) in Python using pyodbc and the following conenction string:
conn = pyodbc.connect("DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\DB.accdb;")
, and no matter whether I keep the OLEDB provider or I use the Provider=MSDASQL; as mentioned here (MS mentions it's not availiable for 64bit), I keep getting the following error:

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

What might cause this problem?

ADD: I have looked into pyodbc docs more closely and tried conn = pyodbc.connect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=c:\\DB.accdb;") - the same error. This is really weird, since the pyodbc.dataSources() shows that I have this provider.

ADD2: I tried win32com.client usage such as here in order to connect by using OLE DB - no success. Seems that it's impossible, nothing works.

Community
  • 1
  • 1
havelock
  • 171
  • 1
  • 1
  • 10

1 Answers1

13

  1. Try to use something like the following instead of using the same string as the one for OLeDb:
    "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\DB.accdb;"

  2. You may not be able to talk to the driver directly from your x64 Python application: Access 2007 and its ACE driver are 32 bits only.
    Instead, get the ACE x64 driver for Access 2010, but be careful that if you already have Access or the ACE driver 32bit installed, it won't work.
    I would stick to the 32bit versions of Python and of the ACE driver if you expect your app to be run on other systems: it is not recommended to mix x64 and x86 versions of Office tools and drivers, you'll probably end up with lots of issues if you do.

  3. If the issue is not with the 32/64bit mix, then maybe this question has the answer you seek.

Community
  • 1
  • 1
Renaud Bompuis
  • 16,596
  • 4
  • 56
  • 86
  • Thank you, I will be aware of x64 compatibility problems. However, for this application I have decided just to port DB to SQLite manually and use it that way - I figured it will potentially solve lots of problems :) Anyway, the DB file is meant to be edited only by application itself. – havelock Jun 19 '11 at 09:14
  • I was editing the answer to add more suggestions. Maybe the issue is just with the connection string? Would save you a rewrite. – Renaud Bompuis Jun 19 '11 at 09:18
  • I have used the connection string you mention in the beginning, see the "ADD" section; creating a user DSN might solve the problem(although I am not really sure that would help, after all all of the drivers/providers have failed, and still I am pretty sure that what you have mentioned in 2nd point is the case, since the original VC++ app is 32bit), but as I have mentioned, I have solved the problem in a more drastic way. – havelock Jun 19 '11 at 09:27
  • Note you can force the installation the 64bit drivers on a machine with 32bit office using the 'passive' switch. Other things might break though!http://stackoverflow.com/questions/7116019/hand-install-of-64-bit-ms-access-odbc-drivers-when-32-bit-office-is-present – RobinL Nov 13 '13 at 13:54