Retrieving Access table names in Python script

Hi Steve!

Yes, you can do it in Python script.

Firstly, the library “pyodbc” should be installed (Python should be surely installed as well). To install the library, please open Command Prompt (Win + R -> cmd) and print the following:

pip install pyodbc

Press Enter. If everything is ok you will see “Successfully installed pyodbc”.

Then you need to check that Microsoft Access Driver is installed on your computer. This component is required for access to Access files (pardon the pun). The easiest way to check it is to run the following Python script:

import pyodbc

print([x for x in pyodbc.drivers() if x.startswith('Microsoft Access Driver')])

If you see an empty list then you are running 64-bit Python and you need to install the 64-bit version of the "ACE" driver. If you only see ['Microsoft Access Driver (*.mdb)'] and you need to work with an .accdb file then you need to install the 32-bit version of the "ACE" driver.

You can download installers  here.

Also, you can face a following problem (I faced it). Windows and Python are 64bit, but Office is 32bit. Here is the best info how to install the driver in this case.

Finally, your script for retrieving Access table names looks like:

import pyodbc

# Please change it to your file path
file_path = r'D:\D_Micromine_Projects\01_Sinuh_Dep_Ves_DataBase.mdb'

# Parameters for connection
conn_str = (
r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};' + \
'DBQ={}'.format(file_path)
)

# Establish a connection with the database
cnxn = pyodbc.connect(conn_str)

# Create a new cursor object using the connection
cursor = cnxn.cursor()

# Create a list of table names and print it
print([table_info.table_name for table_info in cursor.tables(tableType='TABLE')])

python_to_ms_access.py

More information about connecting to Microsoft Access with Python you can find here.

Please let me know if you have any questions!