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

Asked By Nick X

18-Mar-10 01:54 PM

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

18-Mar-10 06:35 PM

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 is called that)?
If so, is the append query attempting to add data to that field? (It
should not)

Are there any other indexes that may be causing problems?

John



--
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

18-Mar-10 02:38 PM

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.

18-Mar-10 02:47 PM

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 will not (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

19-Mar-10 08:04 AM

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...

19-Mar-10 08:08 AM

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.

19-Mar-10 09:01 AM

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.

22-Mar-10 01:05 PM

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