Append Query vs. Copy and Paste

[girky]

Hello!
I have a linked table from a sql server that I am appending to. The table is blank. I have an append query to add records to it that fails due to key violations. If I take the data from the query view of that append query and copy and paste into the linked table it works. I'm going crazy!

There is an autonumber field in the linked table which I'm sure is the problem but I don't know how to fix it. The autonumber field is not listed in my query.

Any help?
Thanks!

 

[HitechUser]

girky,

This may get you closer...

You will receive duplicates only if there is no Primary Key or Index (No) defined on the AutoNumber or Counter field in the table to which you are appending. By definition, primary keys are unique.

Hmmmmmmmmmmmm... Could this be it???

 

[girky]

my primary key is a different field. And I understand the autonumber must be unique too.

But why is an append query different from copying/pasting the append query? Both do not have the autonumber field listed. When i copy/paste it, its automatically filled in. Why wouldn't the append be the same?

And after adding and removing the autonumber field from my append query i now get this "Explicit value must be specified for identity column in table 'tablename' when identity_insert is set to ON"

 

[HitechUser]

girky,
Maybe this will get you closer.

http://support.microsoft.com/default.aspx?scid=kb;en-us;253157

 

[HitechUser]

or this...

http://support.microsoft.com/default.aspx?scid=kb;en-us;229681

 

[girky]

I've fixed my problem!
I added the expression
iif(fieldname="","*",fieldname)
to every column and now I don't get any errors.

So it looks like it has nothing to do with the autonumber field or primary key. I'm still not sure exactly why that fixed it because all the fields are marked as allow nulls with a default value of (null) in the sql table. But i guess the append doesn't see that and copying/pasting does? I don't know, but it's fixed so I'm happy

Thanks HitechUser for your suggestions.