[Chicago] pyodbc to ms access

[Aaron Elmquist]

Noah and Pat

Thanks for the replies and confirming that I am on the correct track with
the 32 bit vs 64 bit version.

Looks like the 32 bit data source manager is buried in a sub folder and
doesn't launch from the control panel.  Thank you MS.

I'll move to a 32-bit version of python and try that.  This really should
not be harder than providing the correct connection string to either pyodbc
as Pat has demonstrated.

Also, I agree with comment on MS Access being weak sauce.  Unfortunately,
it's not my database, and that's where the data exists.

Thanks Again!


On Mon, May 9, 2016 at 10:49 AM, Pat P. <paaschpa at gmail.com> wrote:

> Sample code (below) that 'works on my machine (tm)'. I'm running 32 bit
> Python 2.7.3, MS Access 2007 (can't tell if 32 or 64...shows nothing where
> 'bit' should be...guessing 32 bit?) and Windows 7 64 bit.
>
> import pyodbc
> cnxn = pyodbc.connect(r'DRIVER={Microsoft Access Driver (*.mdb,
> *.accdb)};DBQ=C:\Users\ppaasch\Documents\MSAccessPython\Database1.accdb;')
>
> crsr = cnxn.cursor()
> for table_name in crsr.tables(tableType='Table'):
>   print(table_name)
>
> HTH,
> Pat
>
> On Mon, May 9, 2016 at 7:17 AM, Noah Schultz <noah_schultz at oxfordcorp.com>
> wrote:
>
>> First, he is on the right path about the  32 bit vs 64 bit issue. Second,
>> ms access is weak sauce and he should use a much more robust db with better
>> python integration.
>>
>> Sent from my iPhone
>>
>> On May 9, 2016, at 7:04 AM, Jeremy McMillan <jeremy.mcmillan at gmail.com
>> <mailto:jeremy.mcmillan at gmail.com>> wrote:
>>
>>
>> You probably need to exhume a "Jet" db file format parser. Take a deep
>> breath. Maybe there's ODBC, but probably not.
>>
>> On Mon, May 9, 2016, 06:29 Aaron Elmquist <elmq0022 at umn.edu<mailto:
>> elmq0022 at umn.edu>> wrote:
>>
>> Does anyone have some advice or insight on connecting to an MS Access
>> Database using python?
>>
>> 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<mailto:Chicago at python.org>
>> https://mail.python.org/mailman/listinfo/chicago
>> _______________________________________________
>> Chicago mailing list
>> Chicago at python.org<mailto:Chicago at python.org>
>> https://mail.python.org/mailman/listinfo/chicago
>>
>> The information transmitted, including attachments, is intended only for
>> the person or entity to which it is addressed and may contain confidential
>> and/or privileged material. Any review, retransmission, dissemination or
>> other use of, or taking of any action in reliance upon this information by
>> persons or entities other than the intended recipient is prohibited. If you
>> received this e-mail in error, please notify the sender immediately by
>> replying to the message and deleting the material from your computer.
>> _______________________________________________
>> Chicago mailing list
>> Chicago at python.org
>> https://mail.python.org/mailman/listinfo/chicago
>>
>
>
> _______________________________________________
> 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/6914ee05/attachment.html>