sql query for table names with pandas

In MS Access, you can retrieve metadata on a database using its system table, MSysObjects. Below is a DML call to retrieve all table names:

SELECT MSysObjects.Name
FROM MsysObjects 
WHERE ((MSysObjects.Type)=1)
ORDER BY MSysObjects.Name;

However, by default this will not work with external ODBC calls such as you do in Python as permission is not allowed. To resolve, consider two routes:

Grant Permission (for Admin user)

Inside the MSAccess.exe GUI, open database and run VBA subroutine (in standalone module) which only needs to be run once:

Public Sub GrantMSysPermission()
   Dim strSQL As String
   strSQL = "GRANT SELECT ON MSysObjects TO Admin;"

   CurrentProject.Connection.Execute strSQL
End Sub

Once done run above query in pandas read_sql call.

Saved Table

Inside MS Access.exe GUI program, run below make-table query:

SELECT MSysObjects.Name
INTO DBTables
FROM MsysObjects 
WHERE ((MSysObjects.Type)=1)
ORDER BY MSysObjects.Name;

Then in Python pandas, refer to new table:

cnxn = pyodbc.connect('DRIVER={{Microsoft Access Driver (*.mdb)}};DBQ=' + \
                      '{};Uid=Admin;Pwd=;'.format(str(self.file_selected)))

sql = "SELECT * DBTables"
self.TableNames = pd.io.sql.read_sql(sql, cnxn)
cnxn.close()