Connecting from Python on Linux to MS Access

Question

I am trying to connect to an access .mdb file in a linux environment. Until now, I have done this in windows like this:

import pyodbc

DRIVER="{Microsoft Access Driver (*.mdb, *.accdb)}"

def connect():
   PATH = '...file.mdb'
   con = pyodbc.connect('DRIVER={};DBQ={}'.format(DRIVER,PATH))
  return con

Now trying this in Ubuntu 18, this won't work because the Microsoft access driver is not available. I have been all over trying to solve this, mainly with MDBTools. After installing MDBTools and changing the driver to MDBTools I get this error:

pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'libmdbodbc.so' : file not found (0) (SQLDriverConnect)")

I no idea how to resolve this, sources say download a libmdbodbc package, but it seems this package no longer exists.

Might sound silly, but have you verified that you don't have `libmdbodbc.so` on your system? — Joshua Schlichting, Jul 11 '19 at 16:25
@JoshuaSchlichting So I dont really understand how packages work in linux, so I don't know where that file is *supposed* to be, but I did find it at `/usr/lib/x86_64-linux-gnu/odbc/libmdbodbc.so`. So I also tried changing the driver to that file directly, which gives me the error `pyodbc.Error: ('HY000', 'The driver did not supply an error!')`. Again, no idea if I'm doing anything that makes sense. — SpeedOfRound, Jul 11 '19 at 16:30
This sounds very similar to this issue: https://stackoverflow.com/q/47180843/3589609 The accepted answer here installs an older version of the library needed. Maybe test out something along those lines? — Joshua Schlichting, Jul 11 '19 at 17:07
@JoshuaSchlichting those all have to do with mssql drivers, I have no idea where to get the access mdbodbc file from — SpeedOfRound, Jul 11 '19 at 17:15
@SpeedOfRound - You may be interested in [this answer](https://stackoverflow.com/a/25614063/2144390). — Gord Thompson, Jul 11 '19 at 17:28
@GordThompson this is all pretty over my head, I'm really just trying to get pyodbc to work. I installed jython with ucanaccess but I get the error `zxJDBC.DatabaseError: driver [net.ucanaccess.jdbc.UcanloadDriver] not found` — SpeedOfRound, Jul 11 '19 at 17:52
@SpeedOfRound - I updated my [answer to the other question](https://stackoverflow.com/a/25614063/2144390). Jython has lost steam and JayDeBeApi is now the preferred approach. — Gord Thompson, Jul 15 '19 at 17:53

score -4 · Answer 1 · answered Jul 21 '19 at 13:20

I don't know anything about 'linux environments', whatever that is, but here are a few options that work fine for me.

# MS Access
import pyodbc


conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\your_path_here\\your_DB.accdb;')
cursor = conn.cursor()
cursor.execute('select * from tracking_sales')


for row in cursor.fetchall():
    print (row)

# https://datatofish.com/how-to-connect-python-to-ms-access-database-using-pyodbc/


********  ********  ********  ********  ********  ********  ********  ********  


#import pypyodbc
import pyodbc

# MS ACCESS DB CONNECTION
pyodbc.lowercase = False
conn = pyodbc.connect(
    r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};" +
    r"Dbq=C:\\your_path\\your_DB.mdb;")

# OPEN CURSOR AND EXECUTE SQL
cur = conn.cursor()

# Option 1 - no error and no update
cur.execute("UPDATE dbo_test SET Location = 'New York' Where Status = 'Scheduled'");
conn.commit()


cur.close()
conn.close()


********  ********  ********  ********  ********  ********  ********  ********  


# select records from a table
import pyodbc

conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\your_path\\your_DB.accdb;')
cursor = conn.cursor()
cursor.execute('select * from Table2')

for row in cursor.fetchall():
    print (row)


# insert data into 2 fields in a table    
import pyodbc

conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\Users\\Excel\\Desktop\\Coding\\Microsoft Access\\Split_and_Transpose.accdb;')
cursor = conn.cursor()

cursor.execute(''' INSERT INTO Table2 (ExplodeKey, ExplodeField)
                    VALUES(5, 'RMS')  ''')

conn.commit()


********  ********  ********  ********  ********  ********  ********  ********

"Microsoft Access Driver (*.mdb, *.accdb)" is only available for Microsoft Windows. Other environments (e.g., Linux, macOS) cannot use that driver. — Gord Thompson, Jul 21 '19 at 14:21

Connecting from Python on Linux to MS Access

1 Answers1