Python on accdb - How to Use Python on MS Access Data

In this video, we’re going to show how to use Python to manage data in a MS Access database. We’re going to get the pyodbc module and use it to make a connection, insert a row into a table, and read the contents of a table in a .accdb MS Access database.

Using a database system in your Python projects can greatly increase your ability to handle large amounts of data from many sources for analytics. It allows you to put all this data in one format and leverages the power of RDBMS systems.

However, it isn’t always possible to get access to a data server for your python project. This is where file databases can come in handy. There are several to choose from; sqlite, SQLCE, and MS Access, to name a few. I have used and genuinely like all of these databases. If you learn to use RDBMS in your Python projects, you will really see how much more you can do.

Microsoft Access databases are the most ubiquitous file databases of them all, as Access is installed with MS Office in most corporate environments around the world. Even if you don’t have it, you can download the Microsoft Access Database Engine Redistributable for free from Microsoft in order to do today’s lesson. Access has outlived, outlasted, and outperformed most other formats over the decades of its existence, and it has two file formats; the older .mdb format, and the newer .accdb format.

If you have any questions, please leave them in the comments below!

Follow us on social media:

Get Microsoft Office including Access:

Got a YouTube Channel? I use TubeBuddy, it is awesome. Give it a try:

For developers looking for a new role, check out:

Needing to hire technical resources for your project? Get the best:

Python on ACCDB
How to use Python on MS Access Data
Data Analytics
Python programming
Python with MS Access
PYODBC
coding
data engineering
data science
database
RDBMS
Sean Mackenzie

[cesarmonroy-olivares]

Nice! Simple, short, and to the point. Just what I needed. Thanks!

[vishaltiwari]

best tutorial I had ever seen!! Keep it up bro

[jwcolby]

Very cool. I think it would be helpful to show how to create the dB itself. I know python a little and access a lot. I have always learned towards other dbs in python mostly out of ignorance in how to manipulate the access be in python.

[diogosimoes]

Hello Sean, great video.
Only one doubt, how can we transform the result of the query (ie all the rows) in a data frame?? via pd.DataFrame returned an error

[AuntyMonks]

I'm creating a user input query request. By importing the date, and trying to get it to pull up any names with that date record. However, it keeps bringing up all the names and not my query.
I have select company_name from company where company_found_dates... but I don't know what value to add after the where clause. I've tried >= date/time or >= %s. None works.

[marky]

I'm trying to modify this to accept user input as the filepath and database name but I keep getting errors, even though the string I've created from the user input looks to match the one from the pasted in filepath exactly. Is it possible to use this with user input?

[expat]

This is good but there are many similar videos, and none at all at how to get a dataframe, or csv, or xlsx file into an Access table. is there no other way than to loop thru the source file?

[AllinOne-lhno]

Sir, why I can't install pyodbc module, this is the error shows "command errored out with exit status 1"

[PhilHarmonicus]

This was going great until I ran it, then I got:

"Exception has occurred: InterfaceError
('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')"

I've checked and rechecked the code and it's exactly like yours, except for he path to the database.

[downlaw]

Is it possible for python to list all the installed ODBC drivers? I tried running this code and I get an error that makes me think I don’t have the ODBC driver for Microsoft Access.

[kowsalya_maths]

sir ms access is not here in my system, but i want to open the data using python code. please guide me sir.

[adityarathi]

Hello Sean,

I am having issues while connecting pyodbc to an access database. I am getting error as "The operating System is not presently configured to run this application". I tried to install and uninstall both pyodbc and also the Microsoft Access Database. My access database file is on another machine in server? Can you help me with this particular problem?

[adityarathi]

Hello Sean,

I want to use window functions in MS Access. How can i use a Lag function can you please help me?

[ihssancanmturk]

Wonderful this super wonderful that video tutorial me think ;)

[milenasuarez]

It works!! Thank u so much. Now I just want some columns of my access table, not all of them, how do I do it?

[tigerdataimarticus]

please make playlist on python basic, advance and how can we create a interface through python coding please make a playlist on these topic

[hakimnor]

pyodbc.InterfaceError: ('IM002', '[IM002] [Microsoft][Gestionnaire de pilotes ODBC] Source de données introuvable et nom de pilote non spécifié (0) (SQLDriverConnect)')