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:

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)};'
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'):

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

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,

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$]')


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

Insert Records

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

Update Records

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

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

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


  1. Hi!
    Great tutorial, it helped me a lot!
    But I need to know how do I delimit columns separated by comma.
    Could you please help me?

    1. Hi Torres,

      Thank you for stopping by.
      I’m not sure I understand your question. Can you give more details on what you are trying to achieve?

  2. Hi,

    I have an issue at the very first step of connecting to the access database. when I run the code you wrote I get this message:
    pyodbc.Error: (‘HY024’, “[HY024] [Microsoft][ODBC Microsoft Access Driver] ‘(unknown)’ is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

    the access database I want to import is saved at ‘my documents’ folder on my pc. Could this be the problem? should I save it in the python project’s folder? (I’m completely new to python so I have no idea how this works)

    1. Hi Maya,

      Thanks for your message. Storing your Access database in your document folder shouldn’t be a problem. You just need to include the proper path.
      For example, the “Documents” folder on my PC has the following path: C:\Users\jay\Documents
      Yours will likely be different, just replace “jay” with your own user name on your machine – it could be something looks like this C:\Users\maya\Documents

      Hope that helps, let me know if you have further questions.

  3. Thanks for your video. I noticed that you do not close the database. When I try to replicate your example, I get an error saying that the database is open already.
    Can you help?

  4. This is great walk through. However, I get an error with the following code:

    for row in tDjangoList:
    ‘INSERT INTO TDjango (Eid, Eventtype, Location, Lat, Lon, Created, TMCClosed,FirstArrival(min), PatrolArrival(min), TowArrival(min), LanesCleared(min), RoadwayCleared(min),Camera, DayofWeekOpened, DayofWeekClosed, sameDay, confirmClosed, confirmFirstAr, confirmPtrl, confirmTow, confirmLnClear) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)’,tDjangoList)

    ProgrammingError: (‘The SQL contains 21 parameter markers, but 77 parameters were supplied’, ‘HY000’)

    Not sure where I am going wrong.

Leave a Reply

Your email address will not be published. Required fields are marked *