[Chicago] pyodbc to ms access

[Aaron Elmquist]

Rob, this looks like really good advice.

I got caught up in some other tasks today, but will try to verify your
solution tonight.

Is the yyyy in Database the path to the file?  Do I need so specify a User
ID for xxxx or can I drop that from the string?  There no specific UID
associated with the database.

Obviously, I'm a new to this.

Thanks,

Aaron



On Mon, May 9, 2016 at 11:15 AM, Robert Hamilton <rhamil02 at gmail.com> wrote:

> 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
> _______________________________________________
> Chicago mailing list
> Chicago at python.org
> https://mail.python.org/mailman/listinfo/chicago
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/chicago/attachments/20160509/3ab5e4b2/attachment.html>