list the tables in a database

java, jdbc, ms-access, sql, ucanaccess

Solution

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);
    }
}

Problem

Trying to get a list of all the tables in the .accdb database (2007-2016 format). Can't seem to find the MSysObjects table. Using UCanAccess ``` sql = "SELECT Name FROM MSysObjects WHERE Type = 1 AND Flags = 0"; ResultSet rs = queryChem(sql); public static ResultSet queryChem(String sql) throws SQLException { Statement sChem = connChem.createStatement(); ResultSet rsChem = sChem.executeQuery(sql); return rsChem; } private static void connectChem() { try { Class.forName("net.ucanaccess.jdbc.UcanaccessDriver"); System.out.println("Driver loaded"); String url = "jdbc:ucanaccess://chemdb.accdb"; connChem = DriverManager.getConnection(url); System.out.println("Database Connected - CHEMS"); } catch (Exception ex) { System.out.println("Error"); } } ``` net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.6 user lacks privilege or object not found: MSYSOBJECTS at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:211)

Original source