4

I have python 2.7 32 bit running on a Windows 8.1 64 bit machine.

I have Access 2013 and a .accdb file that I'm trying to access from python and pyodbc.

I can create a 64 bit DSN in the 64 bit ODBC manager. However, when I try to connect to it from python, I get the error:

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

Presumably, python is only looking for 32-bit DSNs and doesn't find the 64-bit one that I've created.

When I try to create a 32-bit DSN within the 32-bit ODBC manager, there is no driver for a accdb file (just .mdb).

I think I need a 32 bit ODBC driver for Access 2013 files (.accdb), but haven't been able to find one.

Is it possible to do what I'm trying to do? -- 32bit python access a Access 2013 .accdb file?

EpicAdv
  • 1,164
  • 1
  • 12
  • 22

3 Answers3

10

32 bit applications including Python can work only with 32 bit ODBC drivers.

64 bit applications including Python can work only with 64 bit ODBC drivers.

If you have:

  • 32 bit Python with pyodbc module
  • 64 bit MS Access ODBC driver

Then you must change something:

  1. You can install 64 bit version of Python (I use Active Python which has odbc module), then you can use 64 bit version of pyodbc module (I see it for Python 2.6, 2.7 and 3.3)
  2. You can install 32 bit version od MS Access driver

Using pyodbc.dataSources() you can list ODBC sources:

sources = pyodbc.dataSources()
dsns = list(sources.keys())
dsns.sort()
sl = []
for dsn in dsns:
    sl.append('%s [%s]' % (dsn, sources[dsn]))
print('\n'.join(sl))

If you use ActiveState Python then you can list them using odbc module as in my recipe: http://code.activestate.com/recipes/578782-printing-list-of-odbc-data-sources/?in=user-186902

RJH2
  • 399
  • 1
  • 6
  • 16
Michał Niklas
  • 53,067
  • 18
  • 70
  • 114
5

I had this same issue. For me, the problem was that I have 32 python and 32 bit pyodbc and 32 bit MS Access. But the pyqt application I created would not run on computers with 64 bit Access.

My solution was to install the 32 bit Access driver (as mentioned by Mikal) found here... http://www.microsoft.com/en-us/download/details.aspx?id=13255 at the command prompt using the "/passive" option. Otherwise, it wouldn't install.

For instance, C:\Downloads\AccessDatabaseEngine.exe /passive The driver is installed and now my application works on the host computer without issue.

There may or may not be issues with both the 32 bit and 64 bit Access drivers being installed. So far, I have not seen any.

AlephNaught
  • 51
  • 1
  • 3
  • Wow.. great this worked for me.. I had Access 2013 64bit and Python 32bit.. with pyodbc.. At first.. the pyodbc connection didn't work due to the 32bit to 64bit issue.. but after installing the AccessDatabaseEngine.exe the pyodbc connection now work very well. Thank you. ( I didn't have to do anything about the /passive thing). – ihightower May 20 '15 at 09:02
  • I have fixed the by installing 32 bit python and access driver of 32 bit. You can check for you installed python by this code. >>> import struct >>> print (8*struct.calcsize("P")) – fakhir hanif Jan 05 '16 at 10:35
2

Trial and error showed that installing the "Access Database Engine" 2007 seemed to create 32-bit ODBC source for Access accdb files.

EpicAdv
  • 1,164
  • 1
  • 12
  • 22
  • If installing the 32-bit version of the Access Database Engine 2007 did not cause problems for your 64-bit version of Access 2013 then that's good news. However, it is always best to have only one version of the Access Database Engine (a.k.a. "ACE") installed on a given machine. – Gord Thompson Jan 28 '14 at 00:29
  • 2
    No where in his comments does he suggest or even imply that access (or ACE) x64 is being used or even installed on the computer. The ONLY information given is he has a 2013 Access file and is creating (for some strange reason) a x64 bit DSN when in fact one should be launching the x32 odbc manager. Installing ACE 2010 or 2013 should also have worked just fine in this case. In fact if this REALLY is a 2013 database, then I would suggest one use the 2013 version of ACE. – Albert D. Kallal Jan 28 '14 at 00:47
  • @AlbertD.Kallal Sorry, no. The second sentence of the question begins "I have Access 2013 **and** a .accdb file...". and the fact that the OP could create a 64-bit DSN means that *some* version of ACE was already installed. – Gord Thompson Jan 28 '14 at 14:08
  • Ok, sure, while I am not 100% sure that creating a x64 DSN means x64 ACE is installed, I can go with this and I think that is a "ok" assuming - so just trying to be sure here. – Albert D. Kallal Jan 29 '14 at 21:44