"Key column information is insufficient..." error even with a PK

[matttsch]

I am using an .adp Access file and I am having trouble editing records through it. My form displays the contains of a table one record at a time. When I try to edit one of the fields of the record, I get the following error after selecting Records>>Refresh or when I try to navigate to the next record:

Key column information is insufficient or incorrect. Too many rows were affected by update.

Despite this error message, the modification is written to the database (I checked with SQL server management studio).

If again try to refresh or navigate away from that record--even if I have not made any additional edits--I get an additional error before the first one:

This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made....(some boring junk)

Once these errors have started, they continue whenever I try to navigate to the next record even though change has already been written to the database. I end up stuck on that record until I restart the app.

How do I get rid of these error messages?

I have read online about not having a primary key on your table will cause this error, but I already have already added a primary key to the table.

If it matters, the field I am attempting to edit is not a part of the primary key.

If it matters, when I check the properties of the form, I see:
Record Source is set to the table's name,
there is a "Filter" applied,
there is a "Order By",
the "Recordset Type" is set to "Updatable Snapshot",
"Server Filter By Form" is set to No, and
"Allow Filters", "Allow Edits", "Allow Deletions", and "Allow Additions" are all Yes.

Details:
OS: MS XP Version 2002, 5.1.2600 Service Pack 2 Build 2600
Access: Access 2003 (11.6566.8122) SP2
Database: Microsoft SQL Server 2005

Thanks,
Matt

 

[RoyVidar]

Disclaimer, I'm on a guessing tour here. I don't use ADPs much, not many do, I think due to MS lack of enthusiasm for it

Since you have a PK, and I assume it is in the recordsource, I'm guessing that you have a trigger. If so, there are two tricks you can do, one, is to set NOCOUNT ON in the trigger, which will suppress the N records affected messages that the server wishes to pass, and if my suspicions are correct, is what Access reacts on. The other, dunno if it works, though, but it's to trap the form error, use the form error event, and do something like

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    If DataERr = <the actual error> Then
        Response = acDataErrContinue
        'Me.Requery ' dunno, try without
    End If
End Sub

Find <the actual error> by doing a Debug.Print DataErr in the event first.

 

[matttsch]

RoyVidar:

Good guess, there is a trigger on the table and adding "SET NOCOUNT ON" in the trigger fixes the problem.

The VB codes does not, however. Adding the code without the Requery, causes no error to be displayed, but you still can't navigate away from the current record. Including the Requery causes a runtime error to appear:

Run-time error '2115':
The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Loader from saving the data in the field.

Trying to mask this error in the same way as before does not work.

Does anyone have a guess how to deal with this problem without editing the triggers. The triggers are used by many more people and I would like to make as small of a change to our system as possible.

RoyVidar, you say that few people use .ADP, this comes as disappointing news to me since my boss asked me to convert our access programs from MDB to ADP because it is suppose to be the newer MS standard. Given that I have little knowledge in this area, I took his word on it. Do you feel that MDB is actually the way to go, or is there a third system better than both?

 

[RoyVidar]

If you can't alter the trigger, and the code thingie doesn't work, then I don't know, I'm afraid.

Here's a thread from one of MS public newsgroups, with a bit of information, thoughts, links and discussions, which also includes some perspectives outside Access/SQL (ADP) http://groups.google.com/group/micr...sqlserver/browse_frm/thread/71ec2636c19c1075/

The current Access - SQL Server recommandation from MS, is to use ODBC linked tables.

Here's also a view from someone who started using ADPs extensively back in 2000 http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/c3d60bf37c9691f0/

It appears there are some issues with the latest version of Access too (2007) and ADPs, the beginning of the following thread, details a bit about that http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/6e16fdbdd0b58c01/

Allow me one quote from the above:
"> To my knowledge, ADP have not been removed from Access 2007; however, it's
> appears that they are now totally screwed in term of speed. .

It's not so bad if you want to have breakfast while things load"

 

[matttsch]

Thanks for the links RoyVidar!