Getting error trying to update record using ADP w/SQL2k

[gvsoest]

I have an ADP project in Access 2003 with SQL 2k back end. I'm trying to update a record thru a form, in which the recordsource happens to be a table with a 2-field primary key. Both of the PK fields are also foreign keys to other tables. I can add new records using the form just fine, but when I try to update one of the key columns on an existing record, I receive the following error:

You can't update the record because another user or application deleted it or changed the value of its primary key

All help on this error points me to creating the table relationships. I have already done this but the error persists. I'm at my wits end as to how to resolve this problem. There has to be a way to update a primary key column on an existing record where that column happens to also be a foreign key to another table.

What's interesting about this is the data actually is updated if I leave the record and come back. I can't help but think there's some kind of switch in Access that I need to flip.

 

[cmmrfrds]

When you say update a primary key, do you mean in the related table where it is the foreign key?

 

[gvsoest]

Sort of. I'm updating the related table where it is a foreign key in the relationship. But this column, along with another column in this table, in combination, form the primary key of the table (it is a classic model of a many-to-many relationship). Let me see if I can illustrate an example...

Table employee:
emp_id (PK)
emp_name

Table chargenumber:
chargeno_id (PK)
chargenumber

Table emp_chargeno:
emp_id (FK)(PK)
chargeno_id (FK)(PK)

In this example I would be attempting to change chargeno_id in the emp_chargeno table.

Hope this helps...

 

[cmmrfrds]

This relationship.
Table emp_chargeno:
emp_id (FK)(PK)
chargeno_id (FK)(PK)

Should be.
Table emp_chargeno:
empCharge_id (PK)
emp_id (FK)
chargeno_id (FK)

Even though the logical key is combined make the physical key of the table a separate field. This will work better with all products such as Access or any other front end product. Access and similiar database products assume you will build the relationship as outlined in the second case. Otherwise you will be contiunally fighting with it.

 

[gvsoest]

How then do you enforce uniqueness (at the database end) of the employee/charge number combination without making them a primary key? I'm using SQL 2k.

 

[cmmrfrds]

You are allowed 1 clustered index on an sql server table where you can combine the 2 fields. Read up on clustered indexes. The records in the table will be in the order of the clustered index so plan whether you want the emp or charge first in the index.

 

[gvsoest]

Problem solved!

I took the suggestion of cmmrfrds and created a unique index (composite of 2 columns), and added a unique primary key column. This helped in solving the problem. But the root of my getting the error message is because of the trigger I was executing on the Update of the table.

My trigger basically updated an "UpdatedBy" and "UpdatedDate" field on the table with a current user and current date/time of the transaction. In my trigger, I'm actually updating the table by joining it with the hidden "deleted" temp table.

Since I was attempting to update one of these fields from the user interface, the join could never happen because they now had differing values, thus It put a halt to my trigger operation. This resulted in the error


You can't update the record because another user or application deleted it or changed the value of its primary key


My join was previously using the two primary key fields. Now that I have the single primary key field, I modified the trigger. An example of this is below...


SET NOCOUNT ON

if not update ( txtUpdatedBy )
begin
update emp_chargeno
set txtUpdatedBy = suser_sname()
from emp_chargeno, deleted
where emp_chargeno.empCharge_id = deleted.empCharge_id
end

if not update ( dtmUpdated )
begin
update emp_chargeno
set dtmUpdated = getdate()
from emp_chargeno, deleted
where emp_chargeno.empCharge_id = deleted.empCharge_id
end


Now that I created the new primary key, I can use that column in my join (as in the example above), and the problem is solved.

Thanks to cmmrfrds for your help in sending me down the right path. I still needed to implement your suggestion in order to fix the problem.

 

[cmmrfrds]

This is an example of the type of problem that can happen if the data is not normalized and using a non-intelligent key for the table. This is the type of things, inter allia, I was alluding to when I said "continue fighting with it".

There is no need to update the primary key on a table that uses a non-intelligent number. This is recommended in most discussions of normalizing data.

Good luck with your application.