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

[Nick X]

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

 

[KARL DEWEY]

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.

 

[vanderghast]

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]

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

 

[Nick X]

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.

 

[Nick X]

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