Matt Matt - 1 month ago 15
Java Question

list the tables in a database

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)

Answer

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