I am trying to insert a simple query into a Microsoft database and i am using UCanAccess

MY CODE:

String databaseURL  = "jdbc:ucanaccess://Orders.accdb";
        
        try {
            Connection connection = DriverManager.getConnection(databaseURL);
            
            System.out.println("Connected to MS Access database");
            
            String sql = "INSERT INTO Orders (Order Date, Completion Date, Starting Date, Machine, Client, Job Name, Comment) VALUES"
                        + "('9/30/2021', '9/30/2021', '9/30/2021', 'Okay', 'Okay', 'Okay', 'Yes you are Okay')";
            
            Statement statement = connection.createStatement();
            int rows =  statement.executeUpdate(sql);
            
            if (rows > 0 ){
                System.out.println("A new thing has been inserted.");
            }
            
            connection.close();
        } catch (SQLException ex) {
            Logger.getLogger(OrdersTestCode.class.getName()).log(Level.SEVERE, null, ex);
        }

MY ERROR

2021-09-30T13:50:45.315+0200  SEVERE  null
net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::5.0.1 user lacks privilege or object not found: ORDER
    at net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate(UcanaccessStatement.java:230)
    at orderstestcode.OrdersTestCode.main(OrdersTestCode.java:29)
Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: ORDER
    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.executeUpdate(Unknown Source)
    at net.ucanaccess.jdbc.ExecuteUpdate.executeWrapped(ExecuteUpdate.java:65)
    at net.ucanaccess.jdbc.AbstractExecute.executeBase(AbstractExecute.java:264)
    at net.ucanaccess.jdbc.ExecuteUpdate.execute(ExecuteUpdate.java:48)
    at net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate(UcanaccessStatement.java:228)
    ... 1 more
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: ORDER
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.ParserDQL.readSimpleColumnName(Unknown Source)
    at org.hsqldb.ParserDQL.readSimpleColumnNames(Unknown Source)
    at org.hsqldb.ParserDML.compileInsertStatement(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)
    ... 7 more

I have put in a breakpoint and it crashes at the int Rows line. I am certain that it might be my SQL statement but im probably wrong. Can someone pls help me figure out what is the problem?

Answer

Try with the correct Access SQL syntax:

String sql = "INSERT INTO Orders ([Order Date], [Completion Date], [Starting Date], Machine, Client, [Job Name], Comment) VALUES "
                 + "(#9/30/2021#, #9/30/2021#, #9/30/2021#, 'Okay', 'Okay', 'Okay', 'Yes you are Okay')";

Advertisement

Answer