Unanswered: 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.
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
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'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