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 will also use a live Access example to demonstrate the connection with Python.

Establishing a Connection between Python and MS 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 3.6:

 

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. Press on the Customize installation:

 

Downloading a 32 bit version of Python 3.6

 

You may want to check the option to install for all users:

 

Python - install for all users

 

Can we now connect our newly and shiny Python to MS Access? Are we there yet?

Almost… you’ll now need to install the pyodbc package that will be used to connect your Python with Access. You can use the PIP install method to install the pyodbc package:

 

Install the pyodbc package

 

If you get the following error when trying to install the pyodbc package, you’ll need to download and then install Microsoft Visual C++ Build Tools:

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

Now let’s look at the steps to connect Python to MS Access.

Steps to connect Python to MS Access using pyodbc

To illustrate how you can connect Python to MS Access, I created the following table in Access:

client_idclient_first_nameclient_last_nameunits_orderedproduct_price_per_unitproduct_name
111BobLee5$500.00Desktop Computer
111BobLee5$200.00Monitor
222JamesFord2$150.00Telephone
333NancySilva3$150.00Telephone
444MariaGreen1$100.00Chair

 

And this is how the table would look like in Access, where the table name is tracking_sales:

 

Connect Python to MS Access Database using pyodbc

 

The Access database is stored on my Desktop under the following path (where testdb is the MS Access file name within that path, while accdb is the MS Access file type):

C:\Users\Doron E\Desktop\Test\testdb.accdb

To connect Python to MS Access:

(1) Open your Python IDLE

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

  • Modify the path name (after the syntax DBQ=) to the path where your Access file is stored. Simply replace the path portion after the syntax DBQ=.  You may wish to add ‘r’ before the ‘Driver=’ portion to address any special characters (such as ‘\’) within the path name
  • Modify the table name (within the ‘select * from tracking_sales‘) to the table name in your Access database

Here is the code that you were waiting for:

 

import pyodbc

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

 

(3) Press F5 to run your code in Python.

Here are the results that I got when running the code. Those results match with the information within my Access table:

 

How to Connect Python to MS Access Database using pyodbc

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 also check the following posts: