B4J Question [Solved] How To Get Tables The List Of Tables In MS Access DB?

[Jorge M A]

Hello, everyone,

I am trying to connect to a MS Access database using the example of this link^.

The connection is made perfectly. Now I need to list the tables contained in that database by running the following command:

Spoiler: SQL Command

SELECT MSysObjects.Name AS table_name
FROM MSysObjects
WHERE (((Left([Name],1))<>"~")
AND ((Left([Name],4))<>"MSys")
AND ((MSysObjects.Type) In (1,4,6))
AND ((MSysObjects.Flags)=0))
order by MSysObjects.Name

I get the error:

Spoiler: Error

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.4 user lacks privilege or object not found: MSYSOBJECTS

I have tried to make the same query through other tools and the result is the same error.
(BTW, I also try to "GRANT SELECT ON MSysObjects TO Admin", with same error).

Searching a lot in google, the same situation using java, is described in these links:
https://www.oipapio.com/question-4437791
https://stackanswers.net/questions/list-the-tables-in-a-database

where they mention that it is not necessary to hit the MsSysobjects table, but to execute these methods on the connection:

'You don't need to hit the [MSysObjects] table. You can get a list of tables via the DatabaseMetaData#getTables method, e.g.,

try (ResultSet rsMD = connChem.getMetaData().getTables(null, null, null, null)) {
    while (rsMD.next()) {
        String tblName = rsMD.getString("TABLE_NAME");
        System.out.println(tblName);
    }
}

My knowledge of java is <= 0, and I don't know how I could translate it to B4J through the object from jSQL library.

Could you help me to implement the solution proposed in B4J?

Thank you very much!

EDIT: my connection string include ;showSchema=true

 

[Jorge M A]

Ok, in case anyone ever requires it, I found out the right query is "SELECT * FROM information_schema.tables".
It seems ucanaccess actually "copies" the original <database>.mdb to an hsqldb one at connection time.