help with append query

[Kathy R.]

What is the best way to handle validation rule violations
when doing an append query? I imported a spreadsheet with
3300 records into my Access 2K db into a new table. I then
tried to append the records from that table into my main
table.... result is lots of validation rule violations and
a broken query. Would really appreciate any help on how
to fix this. thanks!

 

[Michel Walsh]

Hi,



From my experience, it generally occurs if the date_time format is not the
same (which may occur if the Excel spreadsheet is not produce on the same PC
than the PC running the "merge" with the database), or if the decimal
delimiter ( dot or coma) is different, etc. Basically, what Access tell you
is that something is perceived wrong with that data, and mainly, accordingly
to the validations rules your table design enforce (but not Excel), like
incorrect date day-month sequence, making an Ending Date occurring before
the Starting Date, or a negative quantity, or anything expressly built in
the table at design time.

It is generally hard to debug, so, what I did was to try to insert just a
couple of records from Excel, until I find one that produces the error.
Then, if the error is still not evident at that moment, by simply looking at
the data of that single record, take care of its exact appearance, I try to
append MANUALLY a record, in table vie, with the data from the one producing
the error.


Hoping it may help,
Vanderghast, Access MVP