Recover data and stored queries in ms-Access using Java Ucanaccess

Question

I have an Access database. This database contains tables and also stored queries. My goal is to use Java Ucanaccess (a JDBC connector) to use the data stored in the Access file and create reports with Jaspersoft.

Querying on normal tables work:

Connection conn = ConnexionUtils.getConnection();
        Statement stmt = conn.createStatement();
        String query = "select * from Tab_BOUT";
        ResultSet rs = stmt.executeQuery(query);
        int cpt = 0;
        while ( rs.next() ) {
            int numColumns = rs.getMetaData().getColumnCount();
            for ( int i = 1 ; i <= numColumns ; i++ ) {
                if (i > 1) System.out.print(",  ");
                String columnValue = rs.getString(i);
                System.out.print(columnValue + " " + rs.getMetaData().getColumnName(i));
            }
        }

But the problem is that I have objects that use other queries in Access. So when I try to call a Query, it does not work:

"RQT_STORED", for example, contains:

"SELECT Tab_STO_livraisons.[LIV-TIE_num], Req_BOUT_articles_tous.ART_code_EAN FROM (Tab_STO_livraisons INNER JOIN Tab_STO_détails ON (Tab_STO_livraisons.LIV_num = Tab_STO_détails.[STO-LIV_num]) AND (Tab_STO_livraisons.[LIV-TIE_num] = Tab_STO_détails.[STO-TIE_num])) INNER JOIN Req_BOUT_articles_tous ON Tab_STO_détails.[STO-ART_id] = Req_BOUT_articles_tous.ART_code ORDER BY Tab_STO_livraisons.[LIV-TIE_num], Tab_STO_livraisons.LIV_num, Tab_STO_détails.[STO-ART_id]";

The query works in Access, but not in Java. Does anybody know how to do this or has a better solution?

EDIT: Here is a query example: Select * from Req_VENT_librairie_nouvelles_pages_analyse

The real query in access:

SELECT Tab_STO_livraisons.[LIV-TIE_num], Tab_STO_livraisons.LIV_num, Tab_STO_livraisons.LIV_date_livraison, Tab_STO_livraisons.LIV_type_facturation, Tab_STO_détails.[STO-ART_id], Req_BOUT_articles_tous.ART_code_EAN, Tab_STO_détails.STO_nombre, Tab_STO_livraisons.LIV_bdc, Tab_STO_livraisons.LIV_fact_51, Tab_STO_livraisons.LIV_fact_31
FROM (Tab_STO_livraisons INNER JOIN Tab_STO_détails ON (Tab_STO_livraisons.LIV_num = Tab_STO_détails.[STO-LIV_num]) AND (Tab_STO_livraisons.[LIV-TIE_num] = Tab_STO_détails.[STO-TIE_num])) INNER JOIN Req_BOUT_articles_tous ON Tab_STO_détails.[STO-ART_id] = Req_BOUT_articles_tous.ART_code
ORDER BY Tab_STO_livraisons.[LIV-TIE_num], Tab_STO_livraisons.LIV_num, Tab_STO_détails.[STO-ART_id];

and the error message:

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.4 user lacks privilege or object not found: REQ_VENT_LIBRAIRIE_NOUVELLES_PAGES_ANALYSE at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:211) at view.QueryData.main(QueryData.java:32) Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: REQ_VENT_LIBRAIRIE_NOUVELLES_PAGES_ANALYSE at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source) at org.hsqldb.jdbc.JDBCStatement.executeQuery(Unknown Source) at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:208) ... 1 more Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: REQ_VENT_LIBRAIRIE_NOUVELLES_PAGES_ANALYSE at org.hsqldb.error.Error.error(Unknown Source) at org.hsqldb.error.Error.error(Unknown Source) at org.hsqldb.SchemaManager.getTable(Unknown Source) at org.hsqldb.ParserDQL.readTableName(Unknown Source) at org.hsqldb.ParserDQL.readTableOrSubquery(Unknown Source) at org.hsqldb.ParserDQL.XreadTableReference(Unknown Source) at org.hsqldb.ParserDQL.XreadFromClause(Unknown Source) at org.hsqldb.ParserDQL.XreadTableExpression(Unknown Source) at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source) at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source) at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source) at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source) at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source) at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source) at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source) at org.hsqldb.ParserCommand.compilePart(Unknown Source) at org.hsqldb.ParserCommand.compileStatements(Unknown Source) at org.hsqldb.Session.executeDirectStatement(Unknown Source) at org.hsqldb.Session.execute(Unknown Source) ... 4 more

My english is not that good and I tried to translate "Requête Access" to Query, but I think my "RQT_STORED" is actually what you call a "View" — pathat0r, Apr 26 '16 at 15:30
Please [edit] your question to show the actual SQL statement you are using (e.g., `SELECT * FROM RQT_STORED` ...?) and the error message (or better, the full stack trace) that you receive when you try to run it. UCanAccess does intend to support (most) saved SELECT queries, so there may be something peculiar about yours. Also make sure that you are using the latest version of UCanAccess (currently version 3.0.4). — Gord Thompson, Apr 26 '16 at 16:44
For what it's worth, I just tried `SELECT * FROM RQT_STORED` using the query copied verbatim from your question and UCanAccess 3.0.4 had no complaints. — Gord Thompson, Apr 26 '16 at 19:48
@GordThompson, it is actually still not working. I edited my first post! — pathat0r, Apr 28 '16 at 15:02
For information, some of my views are querying a MySQL database. Would that be a big problem? — pathat0r, Apr 29 '16 at 13:34

score 2 · Accepted Answer · answered Apr 29 '16 at 13:42

some of my views are querying a MySQL database. Would that be a big problem?

Yes. UCanAccess can work with Access linked tables (linked tables that point to a table in another Access database) but it cannot work with ODBC linked tables (linked tables that point to a table in an ODBC data source, like MySQL or SQL Server).

Recover data and stored queries in ms-Access using Java Ucanaccess

1 Answers1