apend query

[slimjen1]

All, Using access 2003 frontend and sql server 2008 backend. I have an append query to insert 80000 from one table to an empty table. I get an error:

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

I know this error normally comes if there are dups in a field that doesnt allow.

I checked for duplicates and there are none. I can't seem to find the problem. Any ideas?
Thanks

 

[RainLover]

Create a Copy of the DB and Back End,

Then run the append. If you get an error then detele a Field and try again.

Keep going till you find the Field that is causing the problem.

Not a complete answer but it is a start.

 

[slimjen1]

I think I found something. I ran the query and appended the records that success. Then I ran a unmatched query to find the records that did not append and compare the difference. I found the table in the back end had the datatype on the datefield datetime. The records that did not append had just the date type w/o time. I don't know whether to change the datatype in the backend or the source.
Thanks

 

[RainLover]

This is a SQL Server problem, I think.

Can you change SQL to match Access because you can't change Access.

Change SQL to Date/Time. Back up First.

BTW I am not a SQL expert. But this is what I would on in MySQL