CODEWITHSUNDEEP
sqlvalidationms-accessappendms-access-2013
TMY
TMY

Reputation: 469

Access 2013 - Validation Rule Troubleshooting

I am trying to import data from an old system into an Access backend. In the new Access backend, I have field validation rules on multiple fields.

I've imported the old data into a file dump table. This dump table is then queried to make sure all field values that DO NOT match my validation rules are re-formatted to match the rules. The results of this query are then used in an Append query to add the imported records into the new table with validation rules.

Example SQL Code

INSERT INTO Employees_T ( EmployeeName, [EmpPhone-Home], [EmergencyContactPhone-Mobile])

SELECT 
[myReFormatQuery].[Employee Name] AS EmployeeName, [myReFormatQuery].[Employee HomeNumber-Reformated] AS EmployeeNbr, [myReFormatQuery].[EmergencyContactCell-Reformated] AS EmergencyCell

FROM myReFormatQuery;

My Issue

Most of my records DO NOT append (only 5 of 331). The resulting error message was:

Microsoft Access can't append all the records in the append query..., and 331 record(s) due to validation rule violations.

Troubleshooting

Copy and Paste Query Results -vs- Append Query

My first thought was that I messed up in my initial 're-format' query. To check this wasn't the case (beyond just a visual inspection), I simply ran that query and performed a copy & paste of the results into the table. Doing this, for some strange reason more (142 total) recorded were appended, but the remaining records still didn't paste. The error for those records was:

One or more values are prohibited by the validation rule...

However, at least this resulted in the "Paste Errors" table, which allowed for individual record troubleshooting!

Copy and Paste Individual Paste Error Records

I checked out the records in the paste errors table and nothing looked wrong. So I decided to copy an individual record and paste it... same error.

Copy and Paste Individual Paste Error Fields

Next, I decided to copy & paste the individual fields that were causing the validation error from the record -- magically they paste without a validation rule violation! I pasted all the remaining individual fields into the record, and the record saved perfectly fine.

My Question

Why doesn't my append query work, when obviously the field values meet the validation rule?

This is going to be a regular process while migrating from the old system to the new, so I'd prefer not to go through and manually paste each field for thousands of records and hundreds of thousands of fields. ;)

Any suggestions are greatly appreciated. Thanks!

Upvotes: 0

Views: 415

Answers (1)

TMY
TMY

Reputation: 469

This is not the "answer" to my question (thus I won't mark it as answered hoping someone can provide a better solution for future readers), but I did find a viable solution so I could continue with my project...

  1. Remove validation rules (I copied and pasted in the field description since I had nearly 20 fields with rules).
  2. Run my append query (no issues since there is no validation rule violations)
  3. Add the validation rules back to the table.
  4. Select "Yes" to all three prompts.

This will allow future entries to get checked against the validation rules... but your old data will remain (even though in the prompts of step 4 it notes you have rules violations).

Upvotes: 0

Related Questions