Python: Read Data from MS Access Database via ODBC

Microsoft Access database is commonly used as a file database. It can be used to store small amount of data in your desktop system. To access data in Access database, we can simply use ODBC driver. For Python, we can use pyodbc package.

About Access ODBC driver

As part of the installation of Office Access, the corresponded ODBC drivers are also installed. There are usually both 32 bit and 64 bit installed. You can find it in ODBC Data Source Administrator (via Windows Search):

For this tutorial, I will use the 64 bit version.

About pyodbc

If you have not installed pypdbc package, install it using the following command:

pip install pyodbc

Sample database

I've created a Access database named test.accdb which locates at E:\Documents\test.accdb.

Within this database, it has a table named test_table created with the following 

Now let's read data from this file database using pyodbc.

Read from Access database

Create a Python script  (access-db.py) with the following content:

import pyodbc
import pandas as pd

db_driver = '{Microsoft Access Driver (*.mdb, *.accdb)}'
db_path = 'E:\\Documents\\test.accdb'
conn_str = (rf'DRIVER={db_driver};'
            rf'DBQ={db_path};')

conn = pyodbc.connect(conn_str)

df = pd.read_sql(sql="select * from test_table", con=conn)
print(df)

conn.close()

Execute the script and the following output will be generated:

python .\access-db.py
   ID Attr1  Attr2
0   1     A      0
1   2     B    100
2   3     C    200

File in use error

You may encounter the following error is the database is opened. To fix this error, just simply close it.

pyodbc.Error: ('HY000', "[HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x5b10 Thread 0x5cd8 DBC 0x192400b8
                                              Jet'. (63) (SQLDriverConnect); [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x5b10 Thread 0x5cd8 DBC 0x192400b8                                                              Jet'. (63); [HY000] [Microsoft][ODBC Microsoft Access Driver] Could not use '(unknown)'; file already in use. 
(-1024); [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x5b10 Thread 0x5cd8 DBC 0x192400b8
                              Jet'. (63); [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x5b10 Thread 0x5cd8 DBC 0x192400b8                                                              Jet'. (63); [HY000] [Microsoft][ODBC Microsoft Access Driver] Could not use '(unknown)'; file already in use. (-1024)")

Alternatively, you can also add read mode to the connection string to avoid this issue if you only intend to read data from the database.

conn_str = (rf'DRIVER={db_driver};'
            rf'DBQ={db_path};'
            r'Mode=Read;')

Other SQL statements

Once the connection is established, you can also use Access supported SQL statements to create tables, drop tables, update records, etc. Refer to Microsoft Access SQL reference for more details about the SQL syntax.