How to Connect Python to MS Access Database using pyodbc

Need to connect Python to MS Access database using pyodbc?

If so, I’ll show you the steps to establish this type of connection from scratch!

I’ll also explain how to address common errors when trying to connect Python to Access.

Steps to Connect Python to MS Access using pyodbc

Step 1: Install the pyodbc package

To start, install the pyodbc package that will be used to connect Python with Access. You can use the PIP install method to install the pyodbc package:

pyodbc

Tip: Before you connect Python to Access, you might want to check that your Python Bit version matches with your MS Access Bit version (e.g., use Python 32 Bit with MS Access 32 Bit). You can find additional information about version mismatches at the end of this guide.

Step 2: Create the database and table/s in Access

In order to demonstrate the steps to connect Python to MS Access, I’ll use a simple example where I’ll create:

  • An Access database called: testdb
  • A table called: tracking_sales
  • The tracking_sales table would contain the following columns and data:

How to Connect Python to MS Access Database using pyodbc

Step 3: Connect Python to Access

To connect Python to Access:

(1) Open your Python IDLE

(2) Apply the following code in Python to print the content of your Access table. You’ll need to:

  • Add the path where you stored the Access file (after the syntax DBQ=). Don’t forget to add the MS Access file extension at the end of the path (e.g., ‘accdb’)
  • Add the table name within the select statement
import pyodbc

conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path where you stored the Access file\file name.accdb;')
cursor = conn.cursor()
cursor.execute('select * from table name')
   
for row in cursor.fetchall():
    print (row)

In the context of our example:

  • The Access database is stored on my Desktop under the following path:

C:\Users\Ron\Desktop\Test\testdb.accdb

Where testdb is the MS Access file name within that path, while accdb is the MS Access file extension

  • The table that I added to the select statement is tracking_sales

Here is the code that I used in my case:

import pyodbc

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

Step 4: Run the code in Python

Run the code in Python and you’ll get the same records as stored in the Access table:

How to Connect Python to MS Access Database using pyodbc

In the next section, I’ll explain how to address common errors when trying to connect Python to MS Access.

Addressing Common Errors when Trying to Connect Python to MS Access

Issue 1: Mismatch of versions between Python and Access

When I initially tried to connect Python to MS Access, I got the following error:

Error: (‘IM002’, ‘[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)’)

So why did I get this error?

It happened because I was trying to connect Python 64 Bit with MS Access 32 Bit. Usually, MS Access that will be installed on your computer would be 32 bit.

Due to this mismatch of versions between Python and Access, you won’t be able to establish a connection.

There are different ways to overcome this issue. In my case, I took an easy route by simply downloading a 32 bit version of Python:

Downloading a 32 bit version of Python

Once you proceed with the installation, you’ll notice that the 32 bit version of Python would appear at the top of the installation box:

Downloading a 32 bit version of Python 3.6

Note: If you are using Office 365, you may still get a connectivity issue.

You can then try the following approach in the order specified:

(1) Remove Office 365

(2) Download Microsoft Access Database Engine 2016 Redistributable (pick the AccessDatabaseEngine.exe option, and then install it)

(3) Download the 32-bit version of Office 365, and then install it. You should then be able to connect Python with Access

You may wish to visit the following Microsoft support page that further explains how to tackle this issue.

Issue 2: pyodbc installation error

Originally when I was trying to install the pyodbc package using the pip method, I got the following error:

error: Microsoft Visual C++ 14.0 is required. Get it with “Microsoft Visual C++ Build Tools”

If you get this error, you’ll need to download and then install Microsoft Visual C++ Build Tools.

Conclusion

Once you established a connection between Python and Access, you may ask yourself ‘what’s next’?

What can you do when this connection is alive and kicking?

One option is to use SQL in Python to manage your data…

You can also use Python to insert new values to the MS Access table.

Finally, if you want to learn more about the different types of connections between Python and other database applications, you may check the following posts: