Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 28th, 2004, 10:08 AM
Registered User
 
Join Date: May 2004
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Data has changed error with linked ODBC table

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
 
Old May 30th, 2004, 10:02 PM
Friend of Wrox
 
Join Date: Nov 2003
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

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

 
Old May 31st, 2004, 02:15 PM
Registered User
 
Join Date: May 2004
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old May 31st, 2004, 10:44 PM
Friend of Wrox
 
Join Date: Nov 2003
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

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
 
Old June 1st, 2004, 07:55 AM
Registered User
 
Join Date: May 2004
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old June 1st, 2004, 11:15 AM
Friend of Wrox
 
Join Date: Nov 2003
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

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



 
Old June 2nd, 2004, 02:21 PM
Registered User
 
Join Date: May 2004
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Jemacc,

I tried both Form_BeforeUpdate and Form_AfterUpdate. Only Form_AfterUpdate works.

With kind regards
Wim
 
Old June 8th, 2004, 06:05 AM
Registered User
 
Join Date: May 2004
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Runtime Error 7878 Data has been Changed ayazhoda Access VBA 0 May 22nd, 2008 09:08 AM
ODBC linked table showing old data (not refreshing d-fxt Access 3 November 14th, 2007 05:56 PM
ODBC Linked Table shows #Deleted in all fields Darron Michael Access 4 December 16th, 2005 03:00 PM
ODBC - insert on a linked field - [microsoft][ODBC chimp MySQL 0 April 7th, 2005 04:44 AM
Access - Linked ODBC Tables Ben Access 0 July 24th, 2003 10:39 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.