Using NetBeans IDE's database manager with UCanAccess JDBC driver

Question

I'm trying to use NetBeans' built-in database service system and the UCanAccess JDBC driver to connect to an MS Access database. After dragging and dropping the tables from the service panel to connect them to my JTables in my Swing GUIs everything seems to be working, however as soon as I attempt to run the program I get the following error:

Exception in thread "main" javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: net.ucanaccess.jdbc.UcanaccessSQLException: user lacks privilege or object not found: EXPERIENCETBL Error Code: -5501 Call: SELECT EMPLOYEEID, CORPORATEEXPERIENCE, OPERATIONALEXPERIENCE, PROJECTEXPERIENCE, R_DEXPERIENCE FROM PUBLIC.INFORMATION_SCHEMA.EXPERIENCETBL Query: ReadAllQuery(referenceClass=Experiencetbl sql="SELECT EMPLOYEEID, CORPORATEEXPERIENCE, OPERATIONALEXPERIENCE, PROJECTEXPERIENCE, R_DEXPERIENCE FROM PUBLIC.INFORMATION_SCHEMA.EXPERIENCETBL") at org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException(QueryImpl.java:378) at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:260) at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:469) at Classes.Main_Menu.initComponents(Main_Menu.java:34) at Classes.Main_Menu.(Main_Menu.java:19) at Classes.Master.main(Master.java:21) Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: net.ucanaccess.jdbc.UcanaccessSQLException: user lacks privilege or object not found: EXPERIENCETBL Error Code: -5501 Call: SELECT EMPLOYEEID, CORPORATEEXPERIENCE, OPERATIONALEXPERIENCE, PROJECTEXPERIENCE, R_DEXPERIENCE FROM PUBLIC.INFORMATION_SCHEMA.EXPERIENCETBL Query: ReadAllQuery(referenceClass=Experiencetbl sql="SELECT EMPLOYEEID, CORPORATEEXPERIENCE, OPERATIONALEXPERIENCE, PROJECTEXPERIENCE, R_DEXPERIENCE FROM PUBLIC.INFORMATION_SCHEMA.EXPERIENCETBL") at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:682) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:558) at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2002) at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:570) at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:242) at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228) at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:299) at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:694) at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2738) at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2691) at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:495) at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1168) at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:899) at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1127) at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:403) at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1215) at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2896) at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1804) at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1786) at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1751) at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:258) ... 4 more Caused by: net.ucanaccess.jdbc.UcanaccessSQLException: user lacks privilege or object not found: EXPERIENCETBL at net.ucanaccess.jdbc.UcanaccessConnection.prepareStatement(UcanaccessConnection.java:510) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1556) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1505) at org.eclipse.persistence.internal.databaseaccess.DatabaseCall.prepareStatement(DatabaseCall.java:778) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:619) ... 24 more Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: EXPERIENCETBL at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) at org.hsqldb.jdbc.JDBCPreparedStatement.(Unknown Source) at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source) at net.ucanaccess.jdbc.UcanaccessConnection.prepareStatement(UcanaccessConnection.java:508) ... 28 more Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: EXPERIENCETBL 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.compileStatement(Unknown Source) at org.hsqldb.Session.compileStatement(Unknown Source) at org.hsqldb.StatementManager.compile(Unknown Source) at org.hsqldb.Session.execute(Unknown Source) ... 31 more

After looking through the error I assumed it was most likely a connection error however no error was shown when the connection was created and in the services tab all the tables are shown correctly:

Services Tab

Does anyone know what might be my problem?

Edit: after updating UCanAccess from 2.0.9.5 to 3.0.0 I am still getting the error however it is slightly different

[EL Info]: 2015-08-03 13:19:07.037--ServerSession(193492784)--EclipseLink, version: Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd
[EL Info]: connection: 2015-08-03 13:19:10.113--ServerSession(193492784)--file:/C:/Users/kyle/OneDrive.old/Documents/IT_PAT/IT_PAT_Program/build/classes/_C:\Users\kyle\OneDrive.old\Documents\IT_PAT\IT_PAT_Program\IT_Pat_Database.mdb;showschema=true;PU login successful
[EL Warning]: 2015-08-03 13:19:10.327--UnitOfWork(2027363825)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.0 user lacks privilege or object not found: R
Error Code: -5501
Call: SELECT EmployeeID, CorporateExperience, OperationalExperience, ProjectExperience, R&DExperience FROM PUBLIC.PUBLIC.ExperienceTbl
Query: ReadAllQuery(referenceClass=ExperienceTbl_1 sql="SELECT EmployeeID, CorporateExperience, OperationalExperience, ProjectExperience, R&DExperience FROM PUBLIC.PUBLIC.ExperienceTbl")
Exception in thread "main" javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.0 user lacks privilege or object not found: R
Error Code: -5501
Call: SELECT EmployeeID, CorporateExperience, OperationalExperience, ProjectExperience, R&DExperience FROM PUBLIC.PUBLIC.ExperienceTbl
Query: ReadAllQuery(referenceClass=ExperienceTbl_1 sql="SELECT EmployeeID, CorporateExperience, OperationalExperience, ProjectExperience, R&DExperience FROM PUBLIC.PUBLIC.ExperienceTbl")
    at org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException(QueryImpl.java:378)
    at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:260)
    at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:469)
    at Classes.Main_Menu.initComponents(Main_Menu.java:34)
    at Classes.Main_Menu.<init>(Main_Menu.java:19)
    at Classes.Master.main(Master.java:21)
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.0 user lacks privilege or object not found: R
Error Code: -5501
Call: SELECT EmployeeID, CorporateExperience, OperationalExperience, ProjectExperience, R&DExperience FROM PUBLIC.PUBLIC.ExperienceTbl
Query: ReadAllQuery(referenceClass=ExperienceTbl_1 sql="SELECT EmployeeID, CorporateExperience, OperationalExperience, ProjectExperience, R&DExperience FROM PUBLIC.PUBLIC.ExperienceTbl")
    at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:682)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:558)
    at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2002)
    at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:570)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:242)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:299)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:694)
    at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2738)
    at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2691)
    at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:495)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1168)
    at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:899)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1127)
    at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:403)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1215)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2896)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1804)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1786)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1751)
    at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:258)
    ... 4 more
Caused by: net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.0 user lacks privilege or object not found: R
    at net.ucanaccess.jdbc.UcanaccessConnection.prepareStatement(UcanaccessConnection.java:509)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1556)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1505)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseCall.prepareStatement(DatabaseCall.java:778)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:619)
    ... 24 more
Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: R
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)
    at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
    at net.ucanaccess.jdbc.UcanaccessConnection.prepareStatement(UcanaccessConnection.java:505)
    ... 28 more
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: R
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.ExpressionColumn.checkColumnsResolved(Unknown Source)
    at org.hsqldb.QueryExpression.resolve(Unknown Source)
    at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source)
    at org.hsqldb.ParserCommand.compilePart(Unknown Source)
    at org.hsqldb.ParserCommand.compileStatement(Unknown Source)
    at org.hsqldb.Session.compileStatement(Unknown Source)
    at org.hsqldb.StatementManager.compile(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)
    ... 31 more
Java Result: 1

Once more thanks for any help you can provide.

score 1 · Accepted Answer · answered Aug 02 '15 at 16:20

1

To drag and drop from public schema, and not from the INFORMATION_SCHEMA, solves the issue. It's due to a database metadata bug that has been solved in the 3.0.0.

answered Aug 02 '15 at 16:20

jamadei

1,700
9
8

I tried what you recommend but I'm still coming across the same error. I have deleted the components and re-added them from every single schema and have even tried doing only a single table but still no luck – kyle hendry Aug 03 '15 at 11:02
Yes just have and it still isn't working, I have updated my post to say this. – kyle hendry Aug 03 '15 at 11:38
Ok, so sorry but in this case the ucanaccess behaviour is totally correct. Sql generated isn't because & is a concat operator in access and a column name containing & needs to be escaped. – jamadei Aug 03 '15 at 16:59
Also the persistence engine should use either a backtick , as requested by database metadata or square brackets to escape that name – jamadei Aug 03 '15 at 17:07
thank you for pointing out that issue seems to be working now – kyle hendry Aug 04 '15 at 17:36

Using NetBeans IDE's database manager with UCanAccess JDBC driver

1 Answers1