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.

Answer 1

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()


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