Append key violation error

[sbaldwin]

Hi
Append has worked perfectly until now. I just upgraded to
Windows XP and Office XP. Three of my four append-tables
worked perfectly. One gave the error.

Microsoft Access set 0 field(s) to Null due to a type
conversion failure, and it didn't add x record(s) to the
table due to key violations, 0 record(s) due to lock
violations, and 0 record(s) due to validation rule
violations.

My target tables and to-be-appended tables have the same
structure (and same structure as in former versions)
except the target has one extra field, autonumber on the
primary key.

Is this an upgrade problem?
I saw a fix in the Knowlegebase for a different message
due to validation rule violations but none refering to
this problem.

Microsoft Knowledge Base Article - 302504

There are no null text fields or duplicate keys.

Thanks for any help.
SSB

 

[Dale Fye]

Can you post the text of your SQL? What is the name of your new
autonumber primary key field? You should not include this field in
the append query.

--
HTH

Dale Fye


Hi
Append has worked perfectly until now. I just upgraded to
Windows XP and Office XP. Three of my four append-tables
worked perfectly. One gave the error.

Microsoft Access set 0 field(s) to Null due to a type
conversion failure, and it didn't add x record(s) to the
table due to key violations, 0 record(s) due to lock
violations, and 0 record(s) due to validation rule
violations.

My target tables and to-be-appended tables have the same
structure (and same structure as in former versions)
except the target has one extra field, autonumber on the
primary key.

Is this an upgrade problem?
I saw a fix in the Knowlegebase for a different message
due to validation rule violations but none refering to
this problem.

Microsoft Knowledge Base Article - 302504

There are no null text fields or duplicate keys.

Thanks for any help.
SSB

 

[Akshay Bakhai]

hi ssb

The following seemed to have resolved my problem (although
I don't know if that would be the case for you) - I have
also posted this reply to my previous posting.

I deleted the duplicate records from table2 and re-ran the
query, wherein it worked fine.

So is this a case of a non-user-friendly duplicates
records message from Access where all that he wants to
convey is that there are duplicate records, and thus query
cannot be carried out?

If that is the case then it begs another question from an
Access novice like me, how can one append a table using
APPEND QUERY and inform Access to skip duplicate records?

Best regards

akshay.

 

[John Spencer (MVP)]

Avg is an SQL function and therefore you need to surround the field name with
brackets to force Access to recognize this as a field.

Here is my SQL:

INSERT INTO History_by_UOM_Tons ( Period, UOM, Area,
State, Product, Lo, Hi, [Avg] )
SELECT [2Q03_History_Update].Period,
[2Q03_History_Update].UOM, [2Q03_History_Update].Area,
[2Q03_History_Update].State,
[2Q03_History_Update].Product, [2Q03_History_Update].Lo,
[2Q03_History_Update].Hi, [2Q03_History_Update].Avg
FROM 2Q03_History_Update;

What do the brackets around the final field name in the
append list mean? I cannot find any difference in the data
structure. Data type is currency.

I noticed that after I exit the error message, the field
name for the last column Avg is highlighted in the matrix.

Cheers
SSB

-----Original Message-----
Can you post the text of your SQL? What is the name of your new
autonumber primary key field? You should not include this field in
the append query.

--
HTH

Dale Fye


Hi
Append has worked perfectly until now. I just upgraded to
Windows XP and Office XP. Three of my four append-tables
worked perfectly. One gave the error.


My target tables and to-be-appended tables have the same
structure (and same structure as in former versions)
except the target has one extra field, autonumber on the
primary key.

Is this an upgrade problem?
I saw a fix in the Knowlegebase for a different message
due to validation rule violations but none refering to
this problem.

Microsoft Knowledge Base Article - 302504

There are no null text fields or duplicate keys.

Thanks for any help.
SSB


.