Append Query & Key Violations Error

[MeeMee]

Hi

I have a problem appending data into an oracle table from access. I imported the new data from an excel sheet into a table in access and useed an append query to add the data into a linked oracle table. The linked table has a composit primary key (Code, Org). The table in access has new data that are not in the oracle table and the data in the PK fields are not duplicated. I also made sure that both tables have same data types.

I created an append query that adds the records from the imported table into the linked one. The append query is done successfully on a copy of the linked table, however I keep getting the Key Violation error when I try it on the linked table. It says that access didn't add 932 (All records) record(s) to the table due to key violations. The table has lots of fields but I want to add only the fields in the query below.

Here is my query:

INSERT INTO D7ITEST_R5OBJECTS ( OBJ_OBTYPE, OBJ_OBRTYPE, OBJ_CODE, OBJ_DESC, OBJ_RSTATUS, OBJ_RECORD, OBJ_GROUP, OBJ_USER, OBJ_ORG, OBJ_LOCATION, OBJ_MRC, OBJ_COMMISS, OBJ_STATUS, OBJ_DEPEND, OBJ_PRODUCTION, OBJ_NOTUSED, OBJ_SAFETY, OBJ_STATE, OBJ_RSTATE )
SELECT R5OBJECTS.[OBJ OBTYPE], R5OBJECTS.[OBJ OBRTYPE], R5OBJECTS.[OBJ CODE], R5OBJECTS.[OBJ DESC], R5OBJECTS.[OBJ RSTATUS], R5OBJECTS.[OBJ RECORD], R5OBJECTS.[OBJ GROUP], R5OBJECTS.[OBJ USER], R5OBJECTS.[OBJ ORG], R5OBJECTS.[OBJ LOCATION], R5OBJECTS.[OBJ MRC], R5OBJECTS.[OBJ COMMISS], R5OBJECTS.[OBJ STATUS], R5OBJECTS.[OBJ DEPEND], R5OBJECTS.[OBJ PRODUCTION], R5OBJECTS.[OBJ NOTUSED], R5OBJECTS.[OBJ SAFETY], R5OBJECTS.[OBJ STATE], R5OBJECTS.[OBJ RSTATE]
FROM R5OBJECTS LEFT JOIN D7ITEST_R5OBJECTS ON (R5OBJECTS.[OBJ CODE] = D7ITEST_R5OBJECTS.OBJ_CODE) AND (R5OBJECTS.[OBJ ORG] = D7ITEST_R5OBJECTS.OBJ_ORG);

[FishVal]

Hello, MeeMee.

Your query is expected to return all records from [R5OBJECTS] including those already present in [D7ITEST_R5OBJECTS].
You've missed WHERE clause

... WHERE (D7ITEST_R5OBJECTS.OBJ_CODE IS NULL) AND (D7ITEST_R5OBJECTS.OBJ_ORG IS NULL)
 

Regards,
Fish.

[MeeMee]

Hello, MeeMee.

Your query is expected to return all records from [R5OBJECTS] including those already present in [D7ITEST_R5OBJECTS].
You've missed WHERE clause

... WHERE (D7ITEST_R5OBJECTS.OBJ_CODE IS NULL) AND (D7ITEST_R5OBJECTS.OBJ_ORG IS NULL)
 

Regards,
Fish.

I am still getting the error after adding the where clause, the records I am adding into the linked table are not there in the linked table so it is strange i am getting key violations since the combination of the PK is not there in the linked table

[FishVal]

Do you have any other constraint (but primary key) in the table?
Or maybe triggers?

[MeeMee]

Do you have any other constraint (but primary key) in the table?
Or maybe triggers?

there are some NOT NULL fields but I made sure they have data, some default values on some columns and a unique constraint but they allow null, and some foreign keys, it is a huge table but i am adding data to some fields

[FishVal]

Everything is ok but doesn't work either ... ;) Very common situation.
What could I suggest ...

Being on your place I would first try to fimd circumstances when record could be added to the target table - even remove one existing record and append it back to the table. As soon as a record is being appended succesfully I would analyze difference between this "good" record and a record you try to append from [R5OBJECTS] and make various sequential changes to that "good" record towards "bad" record to localize error source.

Regards,
Fish

P.S. Did you check whether the table have any triggers (particularly append trigger)?

[MeeMee]

Everything is ok but doesn't work either ... ;) Very common situation.
What could I suggest ...

Being on your place I would first try to fimd circumstances when record could be added to the target table - even remove one existing record and append it back to the table. As soon as a record is being appended succesfully I would analyze difference between this "good" record and a record you try to append from [R5OBJECTS] and make various sequential changes to that "good" record towards "bad" record to localize error source.

Regards,
Fish

P.S. Did you check whether the table have any triggers (particularly append trigger)?

Yes , I checked for triggers, it doesn't have any .. I tried adding a new record , only one, again got the same error, I'll try the method you mentioned ..

Thanks alot :)

[MeeMee]

one question, the tables has some foreign keys refrencing other tables, I am not using these fields in the append query, I don't want to assign values to them, could this be causing the problem ??

[FishVal]

one question, the tables has some foreign keys refrencing other tables, I am not using these fields in the append query, I don't want to assign values to them, could this be causing the problem ??

Ok.

I would repeat it once more.
Being on your place I would focus my efforts on localizing error source.
For me it means determining borders of conditions where records addition works, no matter how far they are from what you want to achieve.

Maybe it is better to test first whether you could add record to ANY table - better with some simple table specially created for this. If it works then the problem is specific for your particular table, connection and general database security are not guilty.

Then I would try to add to the target table a record as "good" as possible - all fields have values, PK fields (and other unique indexes) contain unique values, FK fields contain values existing in correspondent tables etc.

And so on in a similar manner.

Regards,
Fish

[MeeMee]

Ok.

I would repeat it once more.
Being on your place I would focus my efforts on localizing error source.
For me it means determining borders of conditions where records addition works, no matter how far they are from what you want to achieve.

Maybe it is better to test first whether you could add record to ANY table - better with some simple table specially created for this. If it works then the problem is specific for your particular table, connection and general database security are not guilty.

Then I would try to add to the target table a record as "good" as possible - all fields have values, PK fields (and other unique indexes) contain unique values, FK fields contain values existing in correspondent tables etc.

And so on in a similar manner.

Regards,
Fish

I managed to add one record by filling in the empty fields and testing, I found that there was one field that needed to be given a value although it is not a mandotry field and accepts Null .. Problem solved :)

thanks alot

[FishVal]

You are welcome, MeeMee.

Best regards,
Fish