Validation Rule Violation with Append Query

[Tameeka]

I am attempting to append to tables with the same fields.
However, when I try to run the append query I get an error
message stating that a validation rule violation has
occurred so that only 13 out of 117 records will append.
When I looked at the table in design view I did not see
any validation rules set. I am not sure what else to do.

 

[Michel Walsh]

Hi,


Validation rules can be that a field is required, and that it won't
accept a zero length string. Also, it may be that the data type is not
right, like floating point numbers that expect a decimal dot and you supply
a coma, or that a date is not valid (due to different mm-dd-yyyy
representation), mainly if you convert from a text representation.

If you try to insert, manually, one of the rejected record, what happen?


Hoping it may help,
Vanderghast, Access MVP

 

[Tim Miller]

I'm experiencing this same thing, but I'm getting NO data. I'm taken a lot
of time to compare the 2 tables and there are no differences. And, I can
paste the data in manually all at once and it works just fine. I've tried
going through my append query and removing one of the fields that I'm
appending, and then re-running the query, over and over again until I tried
every field. No luck.

I'm running a few very similar append queries and they are work just fine.

thanks for any comments

 

[Michel Walsh]

Hi,


Are you using Jet or MS SQL Server. And if you use Jet, DAO
(currentDb, DoCmd) or ADO (CurrentProject.Connection ) ? Jet+ ADO and MS
SQL Server have the tendency to use "transactionnal" append, meaning that if
ONE record fails, the whole batch fails. JET+DAO, on the other hand, may
append those records that do not create problems (as when you do it from the
query designer, or DoCmd, "xxx records were not append due to ... " ). If no
record are added at all, maybe you use a "transactionnal" mode, and ONE
failure make the whole transaction to rollback. If possible, maybe it is
not, but if possible, try with JET+DAO to see if you have the same problem.




Hoping it may help,
Vanderghast, Access MVP