Re: Access complains of duplicate key

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

"David H." <davidh@xxxxxxxxxxxxxxxxx> wrote in message
news:C749B279-0271-4562-B41F-20B5E9C2511B@xxxxxxxxxxxxxxxx
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
two tables into one) for how to fix it? Thanks!


.

Relevant Pages

  • Re: Access 2007->SQL Server2005 "connection was forcibly closed",G
    ... I think it will ameliorate the situation if you clean up the client as ... And to come back to my problem: I think with help of the SQL Server admins ... closed connections - but all of these errors are in the version which used ... Every new client opens again 30 connections if I open 30 tables ...
    (microsoft.public.sqlserver.connect)
  • Re: Best way to populate webpages from multiple tables
    ... tables within hash tables, custom classes etc. ... > Set a session expiring cookie at the client that stores the Customer ID. ... > send it to the client, instead store it in your session mgmt sql server - ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Best way to populate webpages from multiple tables
    ... tables within hash tables, custom classes etc. ... > Set a session expiring cookie at the client that stores the Customer ID. ... > send it to the client, instead store it in your session mgmt sql server - ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Access 2007->SQL Server2005 "connection was forcibly closed",G
    ... an Access database in former times this was a lot of work which took me ... closed connections - but all of these errors are in the version which used ... the SQL Server 2000 and everything worked ... client for programming easy, fast and good applications. ...
    (microsoft.public.sqlserver.connect)
  • Re: SQL and Bandwidth use
    ... In addition to the other responses, take a look at the memory and CPU usage ... in Great Plains Client software. ... > near 100Mb/sec bandwidth). ... Client PC is on same segment as SQL server (not passing through ...
    (microsoft.public.sqlserver.server)
Loading