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:
First_Name | Last_Name | Age |
Jon | Snow | 22 |
Maria | Smith | 34 |
Emma | Jones | 51 |
Bill | Yu | 63 |
Jack | Green | 27 |
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:
First_Name | Last_Name | Age |
Maria | Jordan-Smith | 35 |
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: