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

Discussion in 'B4J Questions' started by Jorge M A, Aug 15, 2019.

  1. Jorge M A

    Jorge M A Active 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:

    Code:
    '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(nullnullnullnull)) {
        
    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: Aug 15, 2019
  2. Jorge M A

    Jorge M A Active 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.
     
    rboeck likes this.
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice