Timestamps causing write conflicts

[gwaddell]

I have an Access XP ADE application connected to a SQL Server 7.0 SP4
database. I have created a timestamp column in the main table.
Unfortunately, I am now getting persistent write conflict errors.

The order of operations are:
1. The application starts and loads the recordset into the form using a
stored procedure.
2. I modify a field and press a save button which uses me.dirty=false to
force a save.
3. The field is saved to the database. Using profiler I can observe the
modified field being saved. As I would expect, the update statement is
using the primary key and the timestamp column value. For the sake of this
discussion let's assume the value of the timestamp is 5ad9.
4. Without navigating off the record, I alter the same field (or a
different field) and press save again and a write conflict will appear.
Using profiler I can see the update statement that is attempting to update
the record. The update statement is using the previous value (5ad9) of the
timestamp column.

I thought that the timestamp column value is incremented each time the
record is updated. The ADE application does not appear to be recognizing
the new timestamp value.

Any help or advice you could give would be appreciated.

Thanks
George

[Steve Jorgensen]

Actually, ADPs/ADEs in Access 2000 have some really serious bugs with TIMESTAMP
columns, and I ended up having to stop using them in my ADP apps. I recommend
that you do the same. Yes, I know all the good reasons you should use
TIMESTAMPS, and I agree with them. Still, you will fail to make an Access 2002
ADP app of any complexity work properly with TIMESTAMP columns in your tables.
Give up now, and save yourself a lot of headaches.

On Tue, 8 Jul 2003 09:13:27 -0500, "gwaddell" <no****@you.know> wrote:

I have an Access XP ADE application connected to a SQL Server 7.0 SP4
database. I have created a timestamp column in the main table.
Unfortunately, I am now getting persistent write conflict errors.

The order of operations are:
1. The application starts and loads the recordset into the form using a
stored procedure.
2. I modify a field and press a save button which uses me.dirty=false to
force a save.
3. The field is saved to the database. Using profiler I can observe the
modified field being saved. As I would expect, the update statement is
using the primary key and the timestamp column value. For the sake of this
discussion let's assume the value of the timestamp is 5ad9.
4. Without navigating off the record, I alter the same field (or a
different field) and press save again and a write conflict will appear.
Using profiler I can see the update statement that is attempting to update
the record. The update statement is using the previous value (5ad9) of the
timestamp column.

I thought that the timestamp column value is incremented each time the
record is updated. The ADE application does not appear to be recognizing
the new timestamp value.

Any help or advice you could give would be appreciated.

Thanks
George

[gwaddell]

Thanks for the information. MS Access is not my regular work area either
and have been lucky
in the past been able to avoid working in it. But my luck has run out.

I've got more experience using ADO in VB 6 and I can use this info there as
well.

"Erland Sommarskog" <so****@algonet.se> wrote in message
news:Xn**********************@127.0.0.1...

gwaddell (no****@you.know) writes:

I have an Access XP ADE application connected to a SQL Server 7.0 SP4
database. I have created a timestamp column in the main table.
Unfortunately, I am now getting persistent write conflict errors.

The order of operations are:
1. The application starts and loads the recordset into the form using a
stored procedure.
2. I modify a field and press a save button which uses me.dirty=false to force a save.
3. The field is saved to the database. Using profiler I can observe
the modified field being saved. As I would expect, the update statement
is using the primary key and the timestamp column value. For the sake
of this discussion let's assume the value of the timestamp is 5ad9.
4. Without navigating off the record, I alter the same field (or a
different field) and press save again and a write conflict will appear.
Using profiler I can see the update statement that is attempting to
update the record. The update statement is using the previous value
(5ad9) of the timestamp column.

I thought that the timestamp column value is incremented each time the
record is updated. The ADE application does not appear to be recognizing the new timestamp value.
But ADE cannot guess what the new value will be, since the timestamp

values are database wide. You must somehow get the new timestamp value back to
ADE when you have done your update.

This is not really my regular playing-ground, but I happened to read this
in a book on ADO Programming as late as today.

For this to work, you need:
. Set cursor-location to adUseClient.
. Set the dynamic property Update Resync to adResyncUpates. (This is a bit mask, so add it to the current value.)
. Set the dynamic property Update Criteria to adCriteriaTimeStamp.


--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[gwaddell]

Yes....I thought that the usual Microsoft help people were very quiet about
this issue.
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:3h********************************@4ax.com...

Actually, ADPs/ADEs in Access 2000 have some really serious bugs with TIMESTAMP columns, and I ended up having to stop using them in my ADP apps. I recommend that you do the same. Yes, I know all the good reasons you should use
TIMESTAMPS, and I agree with them. Still, you will fail to make an Access 2002 ADP app of any complexity work properly with TIMESTAMP columns in your tables. Give up now, and save yourself a lot of headaches.

On Tue, 8 Jul 2003 09:13:27 -0500, "gwaddell" <no****@you.know> wrote:

I have an Access XP ADE application connected to a SQL Server 7.0 SP4
database. I have created a timestamp column in the main table.
Unfortunately, I am now getting persistent write conflict errors.

The order of operations are:
1. The application starts and loads the recordset into the form using a
stored procedure.
2. I modify a field and press a save button which uses me.dirty=false to
force a save.
3. The field is saved to the database. Using profiler I can observe the
modified field being saved. As I would expect, the update statement is
using the primary key and the timestamp column value. For the sake of thisdiscussion let's assume the value of the timestamp is 5ad9.
4. Without navigating off the record, I alter the same field (or a
different field) and press save again and a write conflict will appear.
Using profiler I can see the update statement that is attempting to updatethe record. The update statement is using the previous value (5ad9) of thetimestamp column.

I thought that the timestamp column value is incremented each time the
record is updated. The ADE application does not appear to be recognizing
the new timestamp value.

Any help or advice you could give would be appreciated.

Thanks
George