0

I'm working with Access database (file format *.accdb) and I need to read it from Python code, so I'm using JayDeBeApi.

I'm trying to get the list of all the tables name.

I've tried the query

SELECT MSysObjects.[Name] FROM MSysObjects WHERE Flags = 0 And Type = 1;

But I get net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::5.0.1 user lacks privilege or object not found: MSYSOBJECTS error.

From what I understand, I would need the change user permission on the table MSysObjects, but newer Access files have removed the user permission.

so how can I get the list of the tables names ?

I've also looked at the answers here : list the tables in a database

but this is for Java code and don't know ow to use it with my Python library

My python code is :

# path_to_db: Path to the database
# query: query to execute

u_can_access_jars = [
            f"jdbc_access/ucanaccess-5.0.1.jar",
            f"jdbc_access/commons-lang3-3.8.1.jar",
            f"jdbc_access/commons-logging-1.2.jar",
            f"jdbc_access/hsqldb-2.5.0.jar",
            f"/jdbc_access/jackcess-3.0.1.jar"
        ]
classpath = ";".join(u_can_access_jars)
with jaydebeapi.connect(
            "net.ucanaccess.jdbc.UcanaccessDriver",
            f"jdbc:ucanaccess:///{path_to_db}",
            ["", ""],
            self.classpath
        ) as conn:
    with conn.cursor() as cursor:
        cursor.execute(query)

Thanks

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Geneviève Le Houx
  • 152
  • 1
  • 9
  • If you created a saved query or query definition in Access with the MysyObjects query in it, I think you could then get at that from your API as it would see it as a normal table and not a system object. – Minty Nov 09 '22 at 18:03
  • I found a solution, thanks to https://stackoverflow.com/questions/21027904/using-jpype-how-can-i-access-jdbc-meta-data-functions – Geneviève Le Houx Nov 09 '22 at 21:09

0 Answers0