Search code examples
javasqlucanaccess

DELETE Function in UcanAccess throws strange exception

Since the elimination of the JDBC bridge from Java 1.8 (which we still mourn) and the move to UcanAccess, I've been debugging SQL code that in the past never gave me any issues.. One of the statements are:

DELETE TreatmentRecords.DateGiven, TreatmentRecords.TimeGiven, SInformation.Surname, SInformation.FirstNames, TreatmentRecords.Diagnosis, TreatmentRecords.*
FROM SInformation INNER JOIN TreatmentRecords ON SInformation.SID = TreatmentRecords.SID
WHERE (((TreatmentRecords.DateGiven)=#2015-03-07#) AND ((TreatmentRecords.TimeGiven)='17;16') AND ((SInformation.Surname)='Doe') AND ((SInformation.FirstNames)='John') AND ((TreatmentRecords.Diagnosis)='Headache'));

When executing in Access itself, I get absolutely no errors or problems. However Ucancess throws the following exception:

net.ucanaccess.jdbc.UcanaccessSQLException: unexpected token: TREATMENTRECORDS required: FROM

Any ideas on why would be highly appreciated!

Solution

  • It's a non standard SQL delete statement which is not supported by ucanaccess, even if the Jet engine does. Nothing strange about it. So you have to use the standard SQL for this.

    EDIT e.g., something like this(I haven't added all the conditions):

       DELETE FROM TreatmentRecords tr WHERE 
    tr.DateGiven=#2015-03-07# AND EXISTS 
(SELECT * FROM SInformation s WHERE s.SID=tr.SID AND  s.Surname='Doe')