I have made an ODBC link to a DB2 table.

I can see the records, insert and update them. But, when I update a record, move on to another record (Access will save the changes in the first record automatically) and then return to the first record and make more changes (in the same field or another), I always get a "Data has changed. This record has been changed by another user since you started editing it" error, even when I am the only one working on the DB2 database.

After I click on the OK button of the error message, I can edit the field and other fields without any problem. When I go to another record, the changes are saved to the record. When I return to the record and change a field, I get the same error again.

The table contains
INTEGER, DATE, VARCHAR, SMALLINT, TIMESTAMP, LONG VARCHAR and DECIMAL fields.

The PK is an auto numbering field
ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY

Any idea how I should deal with this?

With kind regards
Wim

Can try to requery (refresh your form) after you update it.

Jemacc,

I am not an Access or VB(A) developer. The "application" is just a form, made by our end users, that opens automatically when the mdb file is opened. The form is based on one big not-normalized table. There is no VB script that can launch a requery request. I moved this table from Access to DB2 because of stability issues when the mdb file was used by multiple concurrent users. This is a temporary solution until our production database and applications are ready to roll out.

I tried this method in test at the end of last year. And it worked flawlessly. When I made the changes last week (for production), I encountered two show-stoppers (the first was a constant "No current record" error each time we tried to update a record, solved by PATCH1=131072). And now this one. It's nice when things behave reproducible.

With kind regards
Wim

In the forms property; create an afterupdate event that requeries (refreshes) the form

docmd.requery
or

Private Sub Form_BeforeUpdate(Cancel As Integer)
    [Forms]![name of your form here].Form.Requery
End Sub

Thanks ! It does the trick in the form. The behaviour is a bit different now. After changing a record, one has to click twice on Next( >) or Previous (<) before the current record actually changes.

Also when altering fields directly in the table, we still get the error of course.

With kind regards
Wim

In my original for I stated afterupdate but the event procedure says beforeUpdate

change it to after update and it see if it works better for you.

Private Sub Form_AfterUpdate(Cancel As Integer)
    [Forms]![name of your form here].Form.Requery
End Sub

Jemacc,

I tried both Form_BeforeUpdate and Form_AfterUpdate. Only Form_AfterUpdate works.

With kind regards
Wim

I've found the reason of the error messages.

We keep bookkeeping information per record about the last time it was updated (TS_MAINT) and who did it (USR_MAINT). Updating the TS_MAINT and USR_MAINT fields is done with a DB2 trigger. The trigger alters the values of these fileds "behind" the back of ACCESS, ACCESS notices these changes and correctly reports that someone else (i.c. DB2) has changed the record.

One extreme solution is to drop the trigger altogether.
But I'll use Jemacc's solution instead. Thanks again !

With kind regards
Wim