Prev: Querying Dates
Next: functions in criteria
From: Nick X Nicknnator on 18 Mar 2010 13:54
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
From: J_Goddard via AccessMonster.com on 18 Mar 2010 14:35
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/Forums.aspx/access-queries/201003/1

From: KARL DEWEY on 18 Mar 2010 14:38
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
From: vanderghast on 18 Mar 2010 14:47
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
news:09DBC882-6B2B-4172-80ED-85BD95777E59(a)microsoft.com...
> 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

 | 
Pages: 1
Prev: Querying Dates
Next: functions in criteria