If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Action Query (Append) didn't add records due to key violations.
Hi all,
I am using MS Access 2007 as front-end and SQL Server 2005 as back-end. In SQL Server I have a PK plus a multi-field index that is unique. I can add records manually in both Access and SQL, but when I try to run an append query it throws a key violation error for the records that in field1 are duplicates but with the addition of field2 in the index are unique. I am perplexed... Thanks in advance, Nick |
#2
|
|||
|
|||
Action Query (Append) didn't add records due to key violations.
Hi -
We need a bit more detail, but - You seem to be assuming that the error is caused by field1+field2 duplicates. Remember that a PK is also "uniquely indexed". What is the PK in the SQL Server database? Is it an Autoincrement field (I think it's called that)? If so, is the append query attempting to add data to that field? (It shouldn't) Are there any other indexes that may be causing problems? John Nick X wrote: Hi all, I am using MS Access 2007 as front-end and SQL Server 2005 as back-end. In SQL Server I have a PK plus a multi-field index that is unique. I can add records manually in both Access and SQL, but when I try to run an append query it throws a key violation error for the records that in field1 are duplicates but with the addition of field2 in the index are unique. I am perplexed... Thanks in advance, Nick -- John Goddard Ottawa, ON Canada jrgoddard at cyberus dot ca Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201003/1 |
#3
|
|||
|
|||
Action Query (Append) didn't add records due to key violations.
If what you are appending looks like this it will error --
field1 field2 A 1 A A B 1 B 2 The second and third records have a null in field2. The third record will give you the violation. -- Build a little, test a little. "Nick X" wrote: Hi all, I am using MS Access 2007 as front-end and SQL Server 2005 as back-end. In SQL Server I have a PK plus a multi-field index that is unique. I can add records manually in both Access and SQL, but when I try to run an append query it throws a key violation error for the records that in field1 are duplicates but with the addition of field2 in the index are unique. I am perplexed... Thanks in advance, Nick |
#4
|
|||
|
|||
Action Query (Append) didn't add records due to key violations.
Jet consider that two null are not duplicated, but MS SQL Server consider
that they are, as far as indexes are concerned. In other words, Jet will accept two null under a field with a UNIQUE constraint on it, but MS SQL Server won't (because it considers that a null duplicates another null). For info, that is one of the point where Jet is accordingly to the standard while MS SQL Server is not (and unlikely to ever be, due to historical behavior). Vanderghast, Access MVP "Nick X" Nicknnator (no) @ (spam) aol.123com wrote in message ... Hi all, I am using MS Access 2007 as front-end and SQL Server 2005 as back-end. In SQL Server I have a PK plus a multi-field index that is unique. I can add records manually in both Access and SQL, but when I try to run an append query it throws a key violation error for the records that in field1 are duplicates but with the addition of field2 in the index are unique. I am perplexed... Thanks in advance, Nick |
#5
|
|||
|
|||
Action Query (Append) didn't add records due to key violations
My Append From Table:
field1 field2 A 2 B 2 C 2 My Append To Table: field1 field2 A 1 B 1 C 1 Field1 is unique in the Append From table, in the Append To table I have created a Unique Non-Clustered Index based on Field1 & Field2. The Append To table has a seperate Unique Clustered Primary Key that is an Auto-Incrementing Integer. Both tables originate from SQL Server (one is in SDE, a GIS DbMS). Nulls are not allowed on either side for Field1 but they are allowed in Field2 in the Append From table. Thanks for your time on this... "KARL DEWEY" wrote: If what you are appending looks like this it will error -- field1 field2 A 1 A A B 1 B 2 The second and third records have a null in field2. The third record will give you the violation. -- Build a little, test a little. "Nick X" wrote: Hi all, I am using MS Access 2007 as front-end and SQL Server 2005 as back-end. In SQL Server I have a PK plus a multi-field index that is unique. I can add records manually in both Access and SQL, but when I try to run an append query it throws a key violation error for the records that in field1 are duplicates but with the addition of field2 in the index are unique. I am perplexed... Thanks in advance, Nick |
#6
|
|||
|
|||
Action Query (Append) didn't add records due to key violations
The PK in the Append To table is allowed to populate on its own, it is not
inclded in the append query. The table has the PK and the aforementioned index only in the indexes. "J_Goddard via AccessMonster.com" wrote: Hi - We need a bit more detail, but - You seem to be assuming that the error is caused by field1+field2 duplicates. Remember that a PK is also "uniquely indexed". What is the PK in the SQL Server database? Is it an Autoincrement field (I think it's called that)? If so, is the append query attempting to add data to that field? (It shouldn't) Are there any other indexes that may be causing problems? John Nick X wrote: Hi all, I am using MS Access 2007 as front-end and SQL Server 2005 as back-end. In SQL Server I have a PK plus a multi-field index that is unique. I can add records manually in both Access and SQL, but when I try to run an append query it throws a key violation error for the records that in field1 are duplicates but with the addition of field2 in the index are unique. I am perplexed... Thanks in advance, Nick -- John Goddard Ottawa, ON Canada jrgoddard at cyberus dot ca Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201003/1 . |
#7
|
|||
|
|||
Action Query (Append) didn't add records due to key violations
I created a union query based on the Append To table and the Append From
table and then ran a find duplicates query on field1 and the number of duplicates matched the number of key violations from the Append query. "vanderghast" wrote: Jet consider that two null are not duplicated, but MS SQL Server consider that they are, as far as indexes are concerned. In other words, Jet will accept two null under a field with a UNIQUE constraint on it, but MS SQL Server won't (because it considers that a null duplicates another null). For info, that is one of the point where Jet is accordingly to the standard while MS SQL Server is not (and unlikely to ever be, due to historical behavior). Vanderghast, Access MVP "Nick X" Nicknnator (no) @ (spam) aol.123com wrote in message ... Hi all, I am using MS Access 2007 as front-end and SQL Server 2005 as back-end. In SQL Server I have a PK plus a multi-field index that is unique. I can add records manually in both Access and SQL, but when I try to run an append query it throws a key violation error for the records that in field1 are duplicates but with the addition of field2 in the index are unique. I am perplexed... Thanks in advance, Nick |
#8
|
|||
|
|||
Action Query (Append) didn't add records due to key violations
OK, I did some more searching and found out that this can also be caused by
trying to set default values such as datetime (sqlserver) so I removed that from my query (setting the date added field) and it seems to have worked. I set the default value in sqlserver for the date field to getdate() (none of my default value settings carried over when I "upsized" my tables). I am hoping this worked and is not just a fluke. Thanks, Nick "vanderghast" wrote: Jet consider that two null are not duplicated, but MS SQL Server consider that they are, as far as indexes are concerned. In other words, Jet will accept two null under a field with a UNIQUE constraint on it, but MS SQL Server won't (because it considers that a null duplicates another null). For info, that is one of the point where Jet is accordingly to the standard while MS SQL Server is not (and unlikely to ever be, due to historical behavior). Vanderghast, Access MVP "Nick X" Nicknnator (no) @ (spam) aol.123com wrote in message ... Hi all, I am using MS Access 2007 as front-end and SQL Server 2005 as back-end. In SQL Server I have a PK plus a multi-field index that is unique. I can add records manually in both Access and SQL, but when I try to run an append query it throws a key violation error for the records that in field1 are duplicates but with the addition of field2 in the index are unique. I am perplexed... Thanks in advance, Nick |
Thread Tools | |
Display Modes | |
|
|