Append query from Access Table to Linked SQL Server Table Failing

Strange one here - I am posting this in both SQL Server and Access forums

Access is telling me it can't append any of the records due to a key violation.

The query:

INSERT INTO dbo_Colors ( NameColorID, Application, Red, Green, Blue )
SELECT Colors_Access.NameColorID, Colors_Access.Application, Colors_Access.Red, Colors_Access.Green, Colors_Access.Blue
FROM Colors_Access;

Colors_Access is linked from another MDB and dbo_Colors is linked from SQL Server 2000.

There are no indexes or foreign contraints on the SQL table. I have no relationships on the dbo_ table in my MDB. The query works if I append to another Access table. The datatypes all match between the two tables though the dbo_ tables has two additional fields not refrenced in the query.

I can manually append the records using cut and paste with no problems.

I have tried re-linking the tables.

Any ideas?
Thanks,
BradI'd guess that the SQL Server db you think you are linking to in dbo_Colors isn't really the one you think. Perhaps the login/password in your datasource is connecting to a different database than the one you expect?

To check, get the name of the constraint being violated and check in the sql server table to see if that constraint exists

Also, try running the sql server profiler to see what sql server db ms access is trying to insert data into|||mattrevs,

It does appear that I am linking the right table. There is only one Colors table and only one database that has a colors table.

Could you tell me how to check which constraint is being violated? After I manually pasted the data into dbo_Colors, I ran checkconstraints() and no error were reported.

I also tried running the query with implicit_transaction OFF and still no joy.

I ran the trace and here are the last two lines. The first one was simply duplicated for each record:

RPC:Completed exec sp_executesql N'INSERT INTO "dbo"."Colors" ("NameColorID","Application","Red","Green","Blue") VALUES (@.P1,@.P2,@.P3,@.P4,@.P5)', N'@.P1 nvarchar(38),@.P2 nvarchar(30),@.P3 int,@.P4 int,@.P5 int', N'{FFC28EAD-1134-40BB-9723-7D88A0B0AC7A}', N'Tile1', 197, 183, 156 Microsoft Access sa 0 11 0 0 2564 54 2004-06-21 10:35:14.170

SQL:BatchCompleted IF @.@.TRANCOUNT > 0 ROLLBACK TRAN Microsoft Access sa 0 0 0 0 2564 54 2004-06-21 10:35:19.403

- Brad|||You say that pasting the data one row at a time from within ms access works ok?

If so, maybe you could also perform a sql trace on this and see what is different?|||I got it figured out. (BTW, I did the trace with the pasting but the results were ... confusing)

Apparently a bit field in SQL server can be Null?

There are two additional fields in the SQL version of the table. The NVarChar field I had set to allow nulls. I never bothered with the Bit field.

When I checked Allow Nulls on the bit field and re-linked, my query worked.

I guess my lesson her is never assume anything. I have learned that lesson many times and will probably learn it again in the (near) future.

Thanks for your help,
Brad