The data has been changed.

[BobD]

I need to be able to edit data and then re-edit that same data 5 seconds
later without getting the dialog "The data has been change. Another user
edited this record and saved the changes before you attempted to save your
changes."

There has GOT to be a way to convince Access to automatically refresh its
lock without annoying me and making me start retyping my changes.

Years of this nonsense and today I've decided I want to find the answer.<g>

 

[Banana]

I need to be able to edit data and then re-edit that same data 5 seconds
later without getting the dialog "The data has been change. Another user
edited this record and saved the changes before you attempted to save your
changes."

There has GOT to be a way to convince Access to automatically refresh its
lock without annoying me and making me start retyping my changes.

Years of this nonsense and today I've decided I want to find the answer.<g>

You need to explain a bit more about what actually happens. What do you
do at every steps leading up to that error? Are there other users
editing the same field?

My guts says that there's probably a user-created code or macro running
behind the form that directs Access to run a query (or something like
that) that edits the same record being edited via the Access UI. This
would trigger a conflict error because it appears to be from two
different sources.

 

[BobD]

Sorry. Wasn't thinking.

Access 2007 at workstation against SQL 2005 database.

Open a linked table and edit a data field. Save the changes by Cliicking
Record Selector.

Now, start to edit that same field and the popup dialog described below
occurs.

I click OK and then I can resume my editing.

This is lame because I edited the data. Worse, this copy of Access edited
the database and doesn't know that WE are the ones who made the change.

I've played with all the Access Options for locking but haven't found
solution. I don't want to change the ODBC refresh to 0 for fear that it'll
bring my system to its knees. Before I try it, I thought I'd ask for help
here.

Thanks!

 

[Banana]

<snip>

Thanks, just one more crucial question remains.

Do you have any code or macros running behind the form. As I described
before, that's almost always the root cause of the bogus write conflict.

 

[Banana]

Thanks, just one more crucial question remains.

Do you have any code or macros running behind the form. As I described
before, that's almost always the root cause of the bogus write conflict.

Sorry. Make that two more questions.

1) Are you sure there is a primary key or at least an unique key defined
and recongized by Access. You can verify this by opening the linked
table in design view. You can't edit stuff in there, of course, but you
can see whether Access has picked up the primary key correctly.

2) Does the source table on SQL Server have any triggers?

 

[Albert D. Kallal]

Sorry. Wasn't thinking.

Access 2007 at workstation against SQL 2005 database.

Open a linked table and edit a data field. Save the changes by Cliicking
Record Selector.

I not sure what you mean by save changes by clicking on a record selector?
Could you expand on this?
Perhaps you mean you moving to another record? Is this a continues form?

To my knowledge, simply clicking on the recordselector will NOT save your
data.

So, are you moving to another record? Or are you working with more then one
form opened on the same record?

I would suggest that you ensure that a timestamp column is added to the
database side.\
(and then delete your table link..and re-link)

I've played with all the Access Options for locking but haven't found
solution. I don't want to change the ODBC refresh to 0 for fear that
it'll
bring my system to its knees. Before I try it, I thought I'd ask for help
here.

No no no, it is not your locking options...don't mess with that stuff...that
is a wild goose chase.

This problem is either you have two forms open to the same reocrd, or you
have some VBA code running that dirites the record. Remember, the current
form you are on has NOT been written to disk (to the table). This only
happens when you move to another reocrd, close the form, or your VBA code
forces a disk write.

So, keep in mind if you run any sql statements or even some recordset code
(ado/dao) that creates an recordset, and updates the same record, then you
get your current message (beucase your current record you are viewing has
NOT been commited to the table, and any other code that modifies the table
direct in place of text boxes and values in the form will thus give you that
error message).

So, if your form text boxes have any after update code running that updates
the table and NOT the fields/text boxes in the form, then that is your
problem.

The two cause/solutions are:
1) You are running code that updates the table in place of the current forms
text boxes. However, the forms current record is thus NOT yet written to
disk. If some code or process updates the table directly, then saving the
forms record will overwrite the data (and you get that message about the
record having been changed). If you have any code that runs, simply force
the current form to be written to the table, and THEN run that update code.
You simply have to ensure that the current forms data is NOT dirty anymore.
You can use:

if me.Dirty = true then
me.Dirty = false
end if
.....code here that runs and updates the table...

2) There is no timestamp column in the database, and that makes it difficult
for access to figure out if the record been changed.

 

[Tony Toews [MVP]]

Access 2007 at workstation against SQL 2005 database.

Do the tables all have a TimeStamp aka RowVersion column?

Tony

 

[Tony Toews [MVP]]

To my knowledge, simply clicking on the recordselector will NOT save your
data.

Yes, it does. Unless behavior is different when going against a SQL
Server database.

Watching the small black right triangle in the record selector change
to a pencil and back to the right triangle are excellent for see
when/if Access updates a record.

Tony

 

[Albert D. Kallal]

Yes, it does. Unless behavior is different when going against a SQL
Server database.

Watching the small black right triangle in the record selector change
to a pencil and back to the right triangle are excellent for see
when/if Access updates a record.

Tony
--

Ah...ok...very good. Thanks for correcting me. I never used that feature. In
most non continues forms, I have the selector turned off. However, In fact,
I was not even aware that clicking on the selector would cause a record
save. Clearly you and the original poster are correct in this regards.
(thanks).

I simply was not aware that doing this would save the record. (it certainly
does). And it easy well to assume that this works 100% as the original
poster points out in sql server also.

So, not a problem here. This does clear up my question quite nicely despite
me not knowing about this!

Since the person is saving the record, and then they start to edit again, My
spider guess moves towards no timestamp, or perhaps other code, or perhaps
the old null-bits issue ? (I don't think that happens anymore???).

And, perhaps the click + jump back to the field occurs so fast that this
error does indeed occur! I not tried this, but it entirely possible that the
problem is reproducible.

to the original poster:

If you click on the record selector to save...and then wait a few seconds,
and then click back on a field, does this error occur? (or is it only when
you do this very fast?). Also, do you have a timestamp column?

 

[BobD]

Yes. PK on all the tables along with Timestamp.

I'm editing the data with built-in datasheet view of tables / queries.

You got me thinking so I created an ACCDB linked to SQL Server's PUBS
database.

Guess what. NO PROBLEMS. Hmmm.

I have I/U/D triggers. The update trigger is rather complex. It handles RI
and quite a few validation steps.

I found the cause!!

-- ---------------
-- Audit Changes
-- ---------------
update T
set UpdatedUserCode = SYSTEM_USER,
UpdatedDate = CURRENT_TIMESTAMP
from TableName E, Inserted I
where T.SerialNum = I.SerialNum

I commented it out and now I can edit in peace! I really thought this
problem was something in Access. I don't have any JET database and all my
tables which I edit constantly have this code.

Thanks, guys!!!

Bob

 

[Banana]

I found the cause!!

Bob, I'm very happy to see you found the cause and applied a fix (or at
least a workaround).

I don't know if it'll still work for you in long term or if you really
need to have an audit change.

This is an area I may err in, but I *think* if you have the validation
in before the fact, it should work. (TBQH, I never really understood SQL
Server's odd "INSTEAD OF" trigger, though I believe they work on
statement-level, rather than row-level which would be why it'd be
different from "BEFORE" triggers used in other RDBMS products...?)

You could replicate the validation in your Access database but that's
quite inelegant way to do it as you certainly don't want to spread your
business logic all over the places. Few possiblities we could look into.

1) See if using an ADO recordset bound to a form makes any differences.
I can't say whether ADO is more receptive to the fact changes has been
made to the same row due to the trigger and thus loses the reference.

2) See if you can get away with refreshing or forcing a move to next or
new record upon the save, the idea being to see whether (in a roundabout
fashion) "re-selecting" the just-edited record help you get around the
fact that row was edited by trigger and not by you/Access.

3) Toss out the timestamp/rowversion. I'm aware almost everyone
recommends this to maintain tracking of the row's edits history; the
only reason I suggest this is because same suggestion has been made in
MySQL (which I've used against as a backend), and I vaguely recall that
I didn't use timestamp (or more accurately, I deigned to not select
those columns into the recordset for the form's binding) and I know I
had triggers upon those tables and they caused no such problem. Due to
my inability to remember why I didn't bother to select the timestamp for
Access's benefit, it may not hurt to test the theory of not selecting
the timestamp/rowversion column into the recordsource and see how it
gets you.

4) Have the form base on the query that doesn't select the columns that
will be updated by the trigger.


I feel there's more but I think my brain clamped on me so see how those
take you along with keeping the trigger logic without that pesky conflict.

 

[Albert D. Kallal]

3) Toss out the timestamp/rowversion. I'm aware almost everyone recommends
this to maintain tracking of the row's edits history;

No, actually here we don't.

The reason why we recommend a timestamp field in a odbc linked table is that
is how access determines that the record has been changed. If you remove
this column, then ms-access has to resort to a field by field comparison. IT
IS STRONGLY RECOMMEND that you continue to keep the timestamp field here.
Don't confuse this issue with any kind of need in sql server for row
versioning. We not using it for that, we don't care about that.

Most sub-forms will behave very badly, if work correctly at all if you don't
have a timestamp column.

This time honored recommendation is not really needed for the sql server
side, but ms-access most certainly relies on this information.