Access complains of duplicate key -- sql server clients

David H.
9/10/2007 12:30:05 PM

Our Access 2000 database (a front end to MSSQL2000 data) has two main tables
with a 1-1 relationship. Call them Client and Assessment. (Why? Mostly
just too many fields to fit in a single table.)
The main data entry form has lots of tabs; the first two tabs look at data
in Client, other tabs use both tables.
Users create a new record using a form which automatically creates records
in both, with the same primary key value (ClientID) in each table. The "New
Client" form then closes and opens the main form, and selects the new client.
They then start entering and saving data happily on tabs 1 and 2. At some
point after that (if they continue entering data without closing that record)
they often get this error:
"ODBC call failed. Violation of Primary Key Constraint (PK_Assessment)
Cannot insert duplicate key in Object 'Assessment'." Any ideas why SQL
thinks I'm trying to insert a duplicate key? Any ideas (other than merging

Russell Fields
9/10/2007 3:49:48 PM

David H.

The problem is with the Access 2000 to SQL Server interface and how Access
decides to insert/update the data. I assume that you are using Access's
native ability to update SQL tables rather than coding your own stored
procedure calls. (But I could be wrong.) However, unless you have
explicitly coded the INSERT and UPDATE statements and their controlling
logic you are not in control of this, but Access is.

The error you are getting is on the PK Constraint, which will not happen
unless you attempt to insert a duplicate key. So, SQL Server thinks you are
inserting a duplicate key because Access is doing so.

Your should probably post this to one of the Access newsgroup for a more
helpful answer about how to work around the problem in Access.

RLF

[quoted text, click to view]

sql server clients : Access complains of duplicate key