Append query failing due to validation errors when no validation rules exist (Access)

Question

I am trying to append data from one table to another in Access 2013. The destination table, [ActionItems], has no records yet.

The source table, [Students], has the necessary columns: [AutoID] (Autonumber), [Action Status] (Short text), [Action Status Last Updated] (Date/time), [Action Status Description] (Short text).

I have set the destination table to match the data types and fields (though the field names are slightly different, I have joined them appropriately in the append query). The [AutoID] field in the destination table is set to Number, Long Integer, as I've read suggested elsewhere.

If I view the append query in datasheet view, I see 731 records, just as I should. But when I try to run the query, I get the following error:

"Microsoft Access can't append all the records... set 0 field(s) to Null due to a type conversion failure... 0... key violations... 0... lock violations, and 731 record(s) due to validation rule violations."

There are NO validation rules in the destination table! Both text fields allow for zero length, and the [AutoID] field has no default value. I don't understand what I could be missing.

Here is my SQL (I realize my column names are rather wordy):

INSERT INTO [ActionItems] ( AutoID, [Action Status], [Action Status Last Updated], [Action Status Description] )
SELECT [Students].[AutoID (Do not use)], [Students].Status, [Students].[Status Last Updated], [Students].[Status Description]
FROM [Students];

If it helps, here is a link to a stripped-down version of the database: https://drive.google.com/file/d/0BysgnYaEVPnJemNnOUc3ZmFsWXM/view?usp=sharing

I have removed all other tables and any identifiable information. The only other major difference is that the primary key in the [Students] table was changed from a composite key ([First Name], [Last Name], and [Email]) to a single primary key ([AutoID (Do not use)]). I made no changes at all to the [ActionItems] table. Even with these changes, the error still occurs exactly as previously described.

Post your sql from your query. That might help figure it out. — randyh22
Validation rules exist not only on field level, but also on table level. In table design, open the property sheet and check the ValidationRule property of the table. — Andre
@Violet Sorry, without being able to see your table design, I can't see anything obviously wrong. — randyh22
Andre, that's a good point... I had forgotten that was even possible. Unfortunately those properties were blank. Based on what randyh22 said, I have updated my question with a link to a stripped-down version of the database itself. I hope that may prove helpful... — user4734199

HansUp HansUp · Accepted Answer · 2016-02-17T21:14:19

The destination table, ActionItems, includes a field named Action Status Last Updated By. That field's Required property is set to Yes, and its Default Value property is blank. That means any time you add a new row to that table, you must supply a value for Action Status Last Updated By.

Notice your INSERT statement does not supply anything for Action Status Last Updated By. Therefore that INSERT does not add any rows.

In my copy of your database, I changed the field's Required property to No. After that change, executing your INSERT query added 731 rows to ActionItems ... one for every row in Students.

If you prefer a different solution, you could leave Required set to True and either put something in the field's Default Value property or alter the INSERT to supply a value for that field.

Append query failing due to validation errors when no validation rules exist (Access)

1 Answers