Adding new records to a table with field set as AutoNumber

[cardgunner]

I'm trying to add a set of records to a table where in that table a field's data type is set to AutoNumber. I can this by copying the records and pasting them into the table. However I would like to simplify the process. I tried doing an append query and it would not let me do it. Actually it error'd saying there where violations and etc do you want to do it anyways. Um sure why not, right. Wrong. It changed my autonumber to number and inserted the records with 0 for that field. Now I can not change the field back to AutoNumber. Arggggg. So after I fix this...

Is there a way to append the table with an append query(or any other query or any other way) so I would not have to copy and paste the new records into the table?

 

[vbaInet]

It's not good practice to duplicate records between two tables. Are you trying to copy certain values?

 

[cardgunner]

I'm struggling with that as well. What I got is the db master customer table. I then get a table sent to me by a third party. Most of those records I have already captured in my master but there are new records that I need to bring over. When it gets brought over and inserted in the master table a unique id gets created (the AutoNumber field) and then I do my work from there. The third party table gets thrown out after I have picked from that the new records.

 

[vbaInet]

What field(s) do you use to determine if that customer already exists? Is there like a unique letter index?

 

[cardgunner]

The third party table does have it's own Unique ID field. So I look for the ones that don't exist in the master and add them.

I did get the append query to work. I had a field that was set as an number in the Master and same field was text in the Third Party. I set the thirdparty.field to isnull where it inserted a 0 where null and then ran the append query and it worked.

However I'm wondering now why when I forced the append did it change my autonumber to a number?

 

[vbaInet]

Have you tried using the Unmatched Query Wizard? That will produce a query with all the records that do not exist in the master table.

It may have changed it to accomodate the different data types. I wouldn't imagine it being changed if the Autonumber field was the primary key.

 

[cardgunner]

Thanks for your help

 

[vbaInet]

You're welcome.