How to Update Records in MS Access using Python

In this short tutorial, I’ll show you how to update records in MS Access using Python.

To start, here is the generic code that you can use to update records in MS Access:

UPDATE Table_Name
SET Column1_Name = value1, Column2_Name = value2,...
WHERE condition

In the next section, I’ll review a simple example with the steps to apply the above code in practice.

Steps to Update Records in MS Access using Python

Step 1: Create a new MS Access database

Let’s now create a simple database, where:

• The database name is: test_database
• This database has only one table called: names_table
• The names_table contains the following data and fields:

This is how the names_table would look like in Access:

Step 2: Connect Python to Access

In the next step, you’ll need to connect Python to Access using the pyodbc package.

You can check the following guide that explains the full steps to connect Python to MS Access.

Below you’ll find the code to connect Python to MS Access. Note that you’ll need to modify the connection string to reflect the location where the Access database is stored on your computer.

In my case, the Access database is stored under this path:

C:\Users\Ron\Desktop\test_database.accdb

And the Python code to connect to Access would look like this:

import pyodbc
 
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\Ron\Desktop\test_database.accdb;')
cursor = conn.cursor()
cursor.execute('select * from names_table')
    
for row in cursor.fetchall():
    print (row)

Once you run the above code, you’ll get the same values as captured in step-1:

Step 3: Update a record in the Access table using Python

Let’s say that you now want to update one record in the Access table using Python.

More specifically, let’s say that you’d like to update Maria’s last name from ‘Smith’ to ‘Jordan-Smith,’ as well as the age to 35. So that the new record should look like this:

Recall that the generic code to update records in MS Access is:

UPDATE Table_Name
SET Column1_Name = value1, Column2_Name = value2,...
WHERE condition

And in the context of our example:

UPDATE names_table 
SET Last_Name = 'Jordan-Smith', Age = 35
WHERE First_Name = 'Maria'

So the complete code to update the record in MS Access using Python is:

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

cursor.execute('''
                UPDATE names_table 
                SET Last_Name = 'Jordan-Smith', Age = 35
                WHERE First_Name = 'Maria'
               ''')
conn.commit()

Don’t forget to add conn.commit() at the end of the code, to ensure that the changes would get implemented.

Step 4: Confirm that the record was updated

Open the names_table in Access, and you would see that Maria’s last name and her age now got updated to the new values: