JDBC/Access: user lacks privilege or object not found

Question

I am trying to insert a tuple from an input relation INPUTRel into a source relation UK_RD_Spend with a foreign key, then update the tuple of the target relation UK_Status. UK_Status and UK_RD_Spend has a primary key-foreign key relation.

Here are the parameters: INPUTRel: TransId, Company, ICB_Code, RD2008 UK_RD_Spend: Company, ICB_Code, RD_spend_2008 UK_Status: Company: RD08

So I am inserting a tuple from INPUTRel into UK_RD_Spend. It succeeded. But when I try to update the tuple of UK_Status, it returned SQLException.

I am trying to update RD08, but I also need to check if foreign key constraint is being violated (if company name doesn't exist in UK_RD_Spend). However, I am getting errors of this:

SQLException: UCAExc:::4.0.3 user lacks privilege or object not found: 

I am not sure how to fix it. The part of my code is as follow:

String queryFK = "SELECT * FROM UK_Status WHERE Company = "
                    + company + "";
            pstmt = conn.prepareStatement(queryFK);
            pstmt.setString(1, company);
            pstmt.setDouble(2, RD2008);
            String msg1;
            try {
                pstmt.executeUpdate();

            } catch (SQLException se) {
                // Check FK Violation: company must exist
                if (t2.get(company).getCompany() == null) {
                // or this ?->
                // if
                // (!(conn.createStatement()).executeQuery(queryFK).next())
                // {
                    String ICB_CodeTest = Integer.toString(ICB_Code);
                    String RD2008Test = Double.toString(RD2008);
                    ICB_CodeTest = ICB_CodeTest.replaceAll("\\s+", "");
                    RD2008Test = RD2008Test.replaceAll("\\s+", "");
                    msg1 = "Transaction "
                            + tid
                            + " failed due to a FK constraint, for the following values of the input; ("
                            + company + ", " + ICB_Code + ", " + RD2008
                            + ")";
                    String fk2 = "INSERT INTO OUTPUTRel"
                            + "(TransId, RunMessages) " + "values (?, ?)";
                    pstmt = conn.prepareStatement(fk2);
                    pstmt.setInt(1, tid);
                    pstmt.setString(2, msg1);
                    pstmt.execute();
                    pstmt.close();
                    continue;
                }
            }
            //
            String companyName = t2.get(company).getCompany();
            Status s = t2.get(companyName);
            s.setRD2008(RD2008);

            String query = "UPDATE UK_Status SET RD08 = ? WHERE Company = ?";
            pstmt = conn.prepareStatement(query);
            pstmt.setDouble(1, s.getRD2008());
            pstmt.setString(2, company);

            int number = (int) s.getRD2008();

            if (number < ICB_Code) {
                msg = "Transaction "
                        + tid
                        + " succeeded for the following values of the input; ("
                        + company + ", " + ICB_Code + ", " + RD2008 + ")";
                pstmt.execute();
                // Finally insert execution result on the output table.
                String oo = "INSERT INTO OUTPUTRel"
                        + "(TransId, RunMessages) " + "values (?, ?)";
                pstmt = conn.prepareStatement(oo);
                pstmt.setInt(1, tid);
                pstmt.setString(2, msg);
                pstmt.execute();
                pstmt.close();
            } else {
                msg = "Transaction "
                        + tid
                        + " failed due to a dynamic constraint, for the following values of the input; ("
                        + company + ", " + ICB_Code + ", " + RD2008 + ")";
                // Finally insert execution result on the output table.
                String dynamicFail = "INSERT INTO OUTPUTRel"
                        + "(TransId, RunMessages) " + "values (?, ?)";
                pstmt = conn.prepareStatement(dynamicFail);
                pstmt.setInt(1, tid);
                pstmt.setString(2, msg);
                pstmt.execute();
                pstmt.close();
            }
        }

Answer 1

String queryFK = "SELECT * FROM UK_Status WHERE Company = "
                + company + "";
        pstmt = conn.prepareStatement(queryFK);
        pstmt.setString(1, company);
        pstmt.setDouble(2, RD2008);
        String msg1;
        try {
            pstmt.executeUpdate();

        } catch (SQLException se) {

Your statement can't be used as PreparedStatement because you put in the value instead of an ?. That's where the error comes from BTW, because you haven't quoted the value, so the database looks for a column with the name of the company rather than looking for a row with the value of company in column `Company'.

Also you execute the statement by using executeUpdate which will not work with a select-statement and will not return a resultset you can iterate over.