[Chicago] pyodbc to ms access

[Robert Hamilton]

Good Morning Aaron,

The trick I've always used on ODBC connections in 64 bit windows is to
use the two ODBC administrator tools.

The 64-bit ODBC Windows admin tool can be found here:
C:\Windows\System32\odbcad32.exe   (If you are running 32-bit Windows,
this will be the only tool present, but then most of these issues
don't happen anyway.)

The 32-bit ODBC Windows admin tool can be found here:
C:\Windows\SysWOW64\odbcad32.exe

What will happen is that if you run a 32-bit application (i.e. MS
Access) it will look at the ODBC connections in that 2nd tool. If you
run a 64-bit application, it will look in the ODBC connections in the
1st tool.

I would suggest going into the 64-bit admin, setting up a connection
pointing to your Access db, and then calling the connection like all
other ODBC connections

cnxn = pyodbc.connect('DSN=<Name you used in creating ODBC connection
in the admin tool above>;Database=yyyyyyyy;UID=xxxxxxx)

Your 64-bit python should then work like normal. This will work well
so long as the location of that Access db doesn't change very often.

I feel like when you try to make the connection on the fly like in
your code below, that it isn't going to default to the correct
bit-version of the drivers. Hence setting it up explicitly in the ODBC
admin tools.

Rob






------previous email-------

Here's my info:

Connection String:

    r'DRIVER={Microsoft Access Driver (*.mdb,
*.accdb)};DBQ=C:\Path\to\DB\db.mdb;'

Python Version:

    Python 3.5.1 :: Continuum Analytics, Inc. - 64 bit

OS

    Windows 7

Office

    32 Bit
Here's my error:
    Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source
name not found and no default driver specified (0) (SQLDriverConnect)')

but...

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

Do I need to use a 32 bit python to get this done?
Can I specify the python bits version to use in a specific conda env?

Thanks,

Aaron