A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Action Query (Append) didn't add records due to key violations.



 
 
Thread Tools Display Modes
  #1  
Old March 18th, 2010, 06:54 PM posted to microsoft.public.access.queries
Nick X
external usenet poster
 
Posts: 66
Default 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  
Old March 18th, 2010, 07:35 PM posted to microsoft.public.access.queries
J_Goddard via AccessMonster.com
external usenet poster
 
Posts: 221
Default 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  
Old March 18th, 2010, 07:38 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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  
Old March 18th, 2010, 07:47 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default 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  
Old March 19th, 2010, 01:04 PM posted to microsoft.public.access.queries
Nick X
external usenet poster
 
Posts: 66
Default 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  
Old March 19th, 2010, 01:08 PM posted to microsoft.public.access.queries
Nick X
external usenet poster
 
Posts: 66
Default 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  
Old March 19th, 2010, 02:01 PM posted to microsoft.public.access.queries
Nick X
external usenet poster
 
Posts: 66
Default 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  
Old March 22nd, 2010, 06:05 PM posted to microsoft.public.access.queries
Nick X
external usenet poster
 
Posts: 66
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:18 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.