For some unclear reason some records are rejected, or field values not set

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

Now you have have a problem. How do you know which records were rejected - or maybe even worse - whose field values have just been ignore (Null)?

What is going wrong? According to the message it knows 4 ways of failing:

note: If the numbers have a Dollar sign at the front or contain commas or spaces between the thousands, the import can fail. Similarly, dates that are not in the standard US format are likely to fail.

The primary key must have a unique value. If you try to import a record where the primary key value is 123, and you already have a record where the primary key is 123, the import fails due to a violation of the primary key.

You can also violate a foreign key. For example, if you have a field that indicates which category a record belongs to, you will have created a table of categories, and established a relationship so only valid categories are allowed in this field. If the record you are importing has an invalid category, you have a violation of the foreign key.

You may have other unique indexes in your table as well. For example, an enrolment table might have a StudentID field (who is enrolled) and a ClassID field (what class they enrolled in), and you might create a unique index on the combination of StudentID + ClassID so you cannot have the same student enrolled twice in the one class. Now if the data you are importing has an existing combination of Student and Class, the import will fail with a violation of this unique index.

The field has the Required property set to Yes, but the data has no value for that field.

The field has the Allow Zero Length property set to No (as it should), but the data contains zero-length-strings instead of nulls.

There is something in the Validation Rule of one of the fields, and the data you are trying to add does not meet this rule. The Validation Rule of each field is in the lower pane of table design window.

There is something in the Validation Rule of the table, and the data you are trying to add does not meet this rule. The Validation Rule of the table is in the Properties box.

If the problem persists, Make sure you have set Default Record Locking to "No Locks" under:    Tools | Options | Advanced (Office Button | Access Options | Advanced in Access 2007.)


4TOPS Import and Validate let's you perform the additions/updates [All Records] and stops at the first record which it can't process to let you fix whatever value needs fixing. It shows you which values it can't accept, and gives the reason.

Update and insert queries - good enough?