Append query : validation error

[BrianBoyes]

I am suffering novices frustration that I can't get something simple to work ! Using Access 2010 database that has been downgraded to 2007 (I imagine from an Excel import)
I have created a table (lets call it Main Table (MT)) from Excel, moved the autoinc. field so as to use the first data field as my primary key ie indexed, no duplicates and reduced the field size to 35 char.
None of the other fields have any kind of validation nor are indexed.
There is a default date field "=Now()" and 2 record modified fields for date & time stamp.
1 field from the import is empty. I have now populated that in Excel and want to append the data into the empty fields of all records where the key values match. I have therefore created a new table (Import table - IT), similarly removed the autoinc field, made the same data field the primary key.
There are now additional records in MT that are not in IT.
I have followed the rules for setting up a simple query and converting it to an append query. The datasheet view shows just the one field whose data I want to import/append. The 2 tables MT & IT are linked in the query on the primary key (same field name, same size, same index).
On running the query I get the error message (in effect) that all records have failed to be appended due to a validation error.
I cannot see any reason for this error message - this suggests that it's something incredibly simple but I still cannot see it !!
Any ideas ?

 

[Beetle]

If you want to modify existing records you use an Update query, not an Append query. An Append query is for inserting new records into a table.

Example SQL for an Update query would look something like;

UPDATE Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID SET Table1.Field1 = Table2.Field1;

 

[Bladerunner]

Had the same problem not long ago, The validation applies also to your primary key field. I had to create a separate record Number (primary key away from my first file. It then worked.

 

[BrianBoyes]

Gentlemen Thank you; the update has (finally) worked although it took me perhaps 2 hours. for others I make the following observations;

The poor use by Microsoft of the English language added unnecessary difficulty - the row "Update to" should be "Update from"
Having been exhorted to check the datasheet view before running the query I did this only to find, time % time again, no records !! As I was expecting the proces to be "Copy/paste" and not "cut/paste" this proved to be a somewhat useless diversion.
The need to re-introduce an autoincrement field, change the primary key and then revrt seemed both complicated and bizare.

However, bing now armed with these peculiarities, the future updates and appends (of which I anticipate many) should be much quicker !