CODEWITHSUNDEEP
javams-accessucanaccess
pathat0r
pathat0r

Reputation: 85

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

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

Upvotes: 0

Views: 673

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123399

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).

Upvotes: 2

Related Questions