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

Jorge M A

Well-Known Member
Licensed User
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:

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:
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:

B4X:
'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
 
Last edited:

Jorge M A

Well-Known Member
Licensed User
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.
 
Upvote 0
Top