Write Conflict with linked Sql Server Tables in Access

[Max]

Hi,

I have SqlServer 2000 as back end and Access 2000 as front-end. All
tables from Sqlserver are linked to Access 2000. I am having write
conflict problem with one of my form which is bound to a query which
consist of let say tableA , TableB and TableC (all are linked tables
from SqlServer. While the form is open I am modifying TableA through
code on Form_AfterUpdate Event and getting the following message.
Write Conflict

This record has been changed by another user since you started editing
it.............

<Save Record> <copy to clipboar> <Drop Changes>

Strange thing is <Save Record> button is disabled so there is no way
user can save chages done to the form.

I know as Iam changing the record while its open is forcing the
message but why the save record is disabled is mystery to me.

I checked all the bit filled on SQLSERVER and changed them to default
zero and updated rest of the table to zero if bit fields are null.
Still I am getting the error.

Please Help

Max

[Trevor Best]

On 12 Sep 2003 13:28:40 -0700 in comp.databases.ms-access,
ma*********@yahoo.com (Max) wrote:

Hi,

I have SqlServer 2000 as back end and Access 2000 as front-end. All
tables from Sqlserver are linked to Access 2000. I am having write
conflict problem with one of my form which is bound to a query which
consist of let say tableA , TableB and TableC (all are linked tables
from SqlServer. While the form is open I am modifying TableA through
code on Form_AfterUpdate Event and getting the following message.
Write Conflict

This record has been changed by another user since you started editing
it.............

<Save Record> <copy to clipboar> <Drop Changes>

Strange thing is <Save Record> button is disabled so there is no way
user can save chages done to the form.

I know as Iam changing the record while its open is forcing the
message but why the save record is disabled is mystery to me.

I checked all the bit filled on SQLSERVER and changed them to default
zero and updated rest of the table to zero if bit fields are null.
Still I am getting the error.

You have make sure the bit fields are "NOT NULL", i.e. don't allow
nulls as well. Re-attach tables afterward.

--
A)bort, R)etry, I)nfluence with large hammer.

(replace sithlord with trevor for email)

[Matthew Sullivan]

I once had that same problem with linked tables, and solved it by
adding a SQL Server "Timestamp" field to each of my tables. (That
means a field with the 'Timestamp' data type.)

-Matt


On 12 Sep 2003 13:28:40 -0700, ma*********@yahoo.com (Max) wrote:

Hi,

I have SqlServer 2000 as back end and Access 2000 as front-end. All
tables from Sqlserver are linked to Access 2000. I am having write
conflict problem with one of my form which is bound to a query which
consist of let say tableA , TableB and TableC (all are linked tables
from SqlServer. While the form is open I am modifying TableA through
code on Form_AfterUpdate Event and getting the following message.
Write Conflict

This record has been changed by another user since you started editing
it.............

<Save Record> <copy to clipboar> <Drop Changes>

Strange thing is <Save Record> button is disabled so there is no way
user can save chages done to the form.

I know as Iam changing the record while its open is forcing the
message but why the save record is disabled is mystery to me.

I checked all the bit filled on SQLSERVER and changed them to default
zero and updated rest of the table to zero if bit fields are null.
Still I am getting the error.

Please Help

Max

[Max]

Matthew Sullivan <Ma**@NoSpam.com> wrote in message news:<u7********************************@4ax.com>. ..

I once had that same problem with linked tables, and solved it by
adding a SQL Server "Timestamp" field to each of my tables. (That
means a field with the 'Timestamp' data type.)

-Matt

About timestamp field do I have to update it with code every time I
change the record or else how it works. I have a limited exposer to
SQL Server. Please Help.

-Max

[Rick Brandt]

"Max" <ma*********@yahoo.com> wrote in message
news:d7**************************@posting.google.c om...

Matthew Sullivan <Ma**@NoSpam.com> wrote in message

news:<u7********************************@4ax.com>. ..

I once had that same problem with linked tables, and solved it by
adding a SQL Server "Timestamp" field to each of my tables. (That
means a field with the 'Timestamp' data type.)

-Matt

About timestamp field do I have to update it with code every time I
change the record or else how it works. I have a limited exposer to
SQL Server. Please Help.

No, A Timestamp in SQL Server is not really something that stores the time or date as
you might expect. It is a long, guaranteed to be unique binary that the engine
automatically changes whenever a row is altered. Once you add one to a table you can
mostly forget about it.

[ChanzReed]

ma*********@yahoo.com (Max) wrote in message news:<d7**************************@posting.google. com>...

Matthew Sullivan <Ma**@NoSpam.com> wrote in message news:<u7********************************@4ax.com>. ..

I once had that same problem with linked tables, and solved it by
adding a SQL Server "Timestamp" field to each of my tables. (That
means a field with the 'Timestamp' data type.)

-Matt

About timestamp field do I have to update it with code every time I
change the record or else how it works. I have a limited exposer to
SQL Server. Please Help.

-Max

No. The problem is some field (most notably date fields) are unstable.
When you open the table for editing SQL takes note of values and will
not allow updates if those values have been changed. The timestamp
field overrides all other values when the table is opened. There's no
code needed whatsoever. Just put a field of data type timestamp in all
your tables and leave it null.

[James]

Thanks! This solved my problem too!

James

un********@aol.com (ChanzReed) wrote in message news:<e2*************************@posting.google.c om>...

ma*********@yahoo.com (Max) wrote in message news:<d7**************************@posting.google. com>...

Matthew Sullivan <Ma**@NoSpam.com> wrote in message news:<u7********************************@4ax.com>. ..

I once had that same problem with linked tables, and solved it by
adding a SQL Server "Timestamp" field to each of my tables. (That
means a field with the 'Timestamp' data type.)

-Matt

About timestamp field do I have to update it with code every time I
change the record or else how it works. I have a limited exposer to
SQL Server. Please Help.

-Max

No. The problem is some field (most notably date fields) are unstable.
When you open the table for editing SQL takes note of values and will
not allow updates if those values have been changed. The timestamp
field overrides all other values when the table is opened. There's no
code needed whatsoever. Just put a field of data type timestamp in all
your tables and leave it null.