How To Connect And Work With MS Access Database Using Python pyodbc

Sharing is caring!

Last Updated on July 14, 2022 by Jay

Python can connect to and work with a wide variety of database applications, MS Access database is one of them. We’ll walk through how to use the pyodbc library to interact with an Access database.

Install pyodbc and check ODBC driver version

pip install pyodbc

TL;DR – You need 32-bit Python for 32-bit Access, or 64-bit Python for 64-bit Access.

One thing to note upfront, if you have 64-bit MS Access, you’ll want to use the 64-bit Python for this exercise. Mixing up a 64-bit Python with 32-bit Access will throw an error when you try to connect. The reason is that there are two different Access ODBC drivers from Microsoft:

1. Old Driver (32-bit) – Microsoft Access Driver (*.mdb): works with 32-bit Python
2. New Driver (64-bit) – Microsoft Access Driver (*.mdb, *.accdb): works with 64-bit Python

Your machine should already have one of the drivers if you have MS Office installed. In case you don’t have the driver, you can download a standalone version on Microsoft’s website: https://www.microsoft.com/en-US/download/details.aspx?id=13255

To check which version of the Access ODBC driver is on your computer, do the following in Python:

>>>import pyodbc
>>>[i for i in pyodbc.drivers() if i.startswith('Microsoft Access Driver')]
['Microsoft Access Driver (*.mdb, *.accdb)']

This list comprehension iterates through all available ODBC drivers and only returns the ones that start with “Microsoft Access Driver”. The above result shows that my computer has the new 64-bit Access (and driver).

Connect Python to MS Access Database

To connect to a database, we need a connection string, basically a text pointer that tells Python where to find the database. For MS Access, we also need to specify the type of ODBC driver (32bit vs 64bit) in the connection string.

Also make sure you close the MS Access database before making the connection, otherwise there will be an error.

conn_str = (r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
            r'DBQ=E:\PythonInOffice\python_connect_to_ms_access\database.accdb;')
conn = pyodbc.connect(conn_str)

Find all tables and queries in the MS Access database

We can loop through all the tables inside the Access database and filter by data tables (‘TABLE’) or queries (‘VIEW’). But seriously, if we can use Python why even bother with Access queries ¯\_(ツ)_/¯

cursor = conn.cursor()
for i in cursor.tables(tableType='TABLE'):
    print(i.table_name)


for i in cursor.tables(tableType='VIEW'):
    print(i.table_name)

Note a “cursor” is an object used to execute SQL statements.

Query The MS Access Database

To interact with a database, we have to use a language that it understands. There’s no exception even if we are using Python to “talk” to the database. This means we have to use SQL. This is not an SQL tutorial so we won’t cover the details. If you need help with SQL the w3schools is a very good site that I used to learn SQL.

#Run SQL statement
cursor.execute('select * from table_name')

#get one row
one_row = cursor.fetchone()

#get all rows
rows = cursor.fetchall()

Use pandas to query

I personally prefer to use pandas to do the query, because most of the time we need to process the data after querying, so pandas is a natural choice. Similar to the method above, we also need a SQL statement, but it’s much easier. As you see below, we don’t have to worry about cursors or fetching results after execution. All we need is that SQL statement and the connection object, then pandas will extract everything (equivalent to fetchall()) for us. The best part is that the result is in a pandas dataframe!

import pandas as pd
df = pd.read_sql('select * from table_name', conn)

Create A New Table

When using SQL statements to modify the database, we have to commit the changes, otherwise, they will not be saved. The below will create a new table with the name “new_table_name”, and specify the column names (column1, column2, column3, …) and their respective data types. However, this is an empty table with no data.

cursor.execute('create table new_table_name (
                                             column1 datatype,
                                             column2 datatype,
                                             column3 datatype,
                                             ....
                                             )')
cursor.commit()

Load Excel data into MS Access

cursor.execute('select * into new_table_name'
               'from [Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=E:\PythonInOffice\python_connect_to_ms_access\richest10.xlsx].[Sheet1$]')

cursor.commit()

Delete A Table

To delete an entire table, it’s probably easier to just use the SQL way. There’s no effective pandas method for this operation.

cursor.execute('DROP TABLE table_name')
cursor.commit()

Insert Records

cursor.execute('INSERT INTO table_name (column1, column2, column3 ...)'
               'VALUES ('value1', 'value2', 'value3' ...)')
cursor.commit()

Update Records

cursor.execute('UPDATE table_name'
               'SET column1 = value1, column2 = value2, ...'
               'WHERE condition')
cursor.commit()

Delete Records

Be careful with the delete statement because if we omit the keyword “WHERE”, all records from that table will be deleted.

cursor.execute('DELETE FROM table_name'
               'WHERE condition')
cursor.commit()

Final thoughts

I only use Access when I absolutely have to, and I don’t recommend it for any projects if I have a choice. My go-to database application for personal & work projects is called SQLITE, stay tuned and we’ll talk about that in the next tutorial.

Additional Resources

How To Connect And Work With SQLite Database Using Python sqlite3