[Chicago] pyodbc to ms access

[Noah Schultz]

Good luck. Glad you found the folder (things like that lead to the "weakness" comment).... Seem to recall another work around, and if I find it I will let you know.

Sent from my iPhone

On May 9, 2016, at 11:04 AM, Aaron Elmquist <elmq0022 at umn.edu<mailto:elmq0022 at umn.edu>> wrote:

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<mailto: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<mailto: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><mailto: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><mailto: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><mailto: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><mailto: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<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


_______________________________________________
Chicago mailing list
Chicago at python.org<mailto:Chicago at python.org>
https://mail.python.org/mailman/listinfo/chicago