Form error????

[jubiiab via AccessMonster.com]

Hi

I just added two checkboxes on my main form and on the MS SQL server I also
added two fields as “bit†format.

Now when I update any data I keep getting this message:
"The record has been changed by another user since you started editing it. If
you save the record, you will overwrite the changes the other user made.
Copying the changes to the
clipboard will let you look at the values the other user entered, and then
paste your changes back in if you decide to make changes…."

What am I doing wrong, because I am sure about that no other user have
changed the record since I was started to editing it.

When I delete those two checkboxes it works fine??????

 

[Jeff Boyce]

You don't mention which versions of Access and/or SQL Server you are using,
so this 'fix' may not apply...

I seem to recall an issue with an earlier version of Access/SQL Server
involving bit fields ... and the solution was to add another field to the
SQL Server table, making it TimeStamp datatype.

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[jubiiab via AccessMonster.com]

Hi Jeff

Thanks for your replay. I am using Access 2003 and the SQL server is...i
think it's 2000....i am not sure. I will look at it tomorrow when I am at
work.

So I just need to add a new field on the main table as timeStamp...hmmm okay,
I will try that and tell you if it workes.

You don't mention which versions of Access and/or SQL Server you are using,
so this 'fix' may not apply...

I seem to recall an issue with an earlier version of Access/SQL Server
involving bit fields ... and the solution was to add another field to the
SQL Server table, making it TimeStamp datatype.

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi

[quoted text clipped - 14 lines]

When I delete those two checkboxes it works fine??????

 

[Tom Wickerath]

Hi Jul

I think this is the KB article that describes the problem:

You may receive write conflict errors when you update records
of a linked SQL Server table (in Access 2000)
http://support.microsoft.com/kb/280730

Note: I added the parentheses around "in Access 2000", as this same issue
very likely applies to all versions of Access and SQL Server.

Adding a field to the SQL Server table of data type Timestamp is probably a
good thing to do. This is not a normal date/time value; it is a GUID
(Globally Unique Identifier) that SQL Server will generate without any action
needed on your part. However, if you are going to use a bit field in the
Access application, I would set a default value of zero. Many Access
developers refuse to use Yes/No fields for this very reason, among them
Access MVP Allen Browne.

Why I stopped using Yes/No fields
http://allenbrowne.com/NoYesNo.html

Access MVP Armen Stein has some good material available at his download
site, including the pros and cons of adding Timestamp fields:

http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp
---> See "The Best of Both Worlds: Access-SQL Server Optimization".


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[jubiiab via AccessMonster.com]

Thx guys - your replays really helped me.

I am using Access 2003 and MS SQL server 2000.

I added those two fields as bit format and another on the SQL sever as
Timestamp format.

In my form I added those 3 fields manually in the forms properties - record
source.

It is working now but I notice one thing. On the SQL sever it writes 0 or 1
but in Access database view it writes 0 or -1 (minus), but I guess it’s the
same!

Hi Jul

I think this is the KB article that describes the problem:

You may receive write conflict errors when you update records
of a linked SQL Server table (in Access 2000)
http://support.microsoft.com/kb/280730

Note: I added the parentheses around "in Access 2000", as this same issue
very likely applies to all versions of Access and SQL Server.

Adding a field to the SQL Server table of data type Timestamp is probably a
good thing to do. This is not a normal date/time value; it is a GUID
(Globally Unique Identifier) that SQL Server will generate without any action
needed on your part. However, if you are going to use a bit field in the
Access application, I would set a default value of zero. Many Access
developers refuse to use Yes/No fields for this very reason, among them
Access MVP Allen Browne.

Why I stopped using Yes/No fields
http://allenbrowne.com/NoYesNo.html

Access MVP Armen Stein has some good material available at his download
site, including the pros and cons of adding Timestamp fields:

http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp
---> See "The Best of Both Worlds: Access-SQL Server Optimization".

Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Hi Jeff

[quoted text clipped - 4 lines]

So I just need to add a new field on the main table as timeStamp...hmmm okay,
I will try that and tell you if it workes.

 

[Jeff Boyce]

You really don't need to care whether a -1 or a 1 is stored, as long as
Access remembers that non-zeros are TRUE. (and yes, Microsoft used "-1" in
Access and "1" in SQL-Server ... go figure?!)

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thx guys - your replays really helped me.

I am using Access 2003 and MS SQL server 2000.

I added those two fields as bit format and another on the SQL sever as
Timestamp format.

In my form I added those 3 fields manually in the forms properties -
record
source.

It is working now but I notice one thing. On the SQL sever it writes 0 or
1
but in Access database view it writes 0 or -1 (minus), but I guess it's
the
same!

Hi Jul

I think this is the KB article that describes the problem:

You may receive write conflict errors when you update records
of a linked SQL Server table (in Access 2000)
http://support.microsoft.com/kb/280730

Note: I added the parentheses around "in Access 2000", as this same issue
very likely applies to all versions of Access and SQL Server.

Adding a field to the SQL Server table of data type Timestamp is probably
a
good thing to do. This is not a normal date/time value; it is a GUID
(Globally Unique Identifier) that SQL Server will generate without any
action
needed on your part. However, if you are going to use a bit field in the
Access application, I would set a default value of zero. Many Access
developers refuse to use Yes/No fields for this very reason, among them
Access MVP Allen Browne.

Why I stopped using Yes/No fields
http://allenbrowne.com/NoYesNo.html

Access MVP Armen Stein has some good material available at his download
site, including the pros and cons of adding Timestamp fields:

http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp
---> See "The Best of Both Worlds: Access-SQL Server Optimization".

Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Hi Jeff

[quoted text clipped - 4 lines]

So I just need to add a new field on the main table as timeStamp...hmmm
okay,
I will try that and tell you if it workes.

 

[Tom Wickerath]

In addition to Jeff's comments, this is the reason that I have gotten in the
habit of testing for <>0 ("Not equal to zero"), instead of testing for = -1
or = True, when I want to retrue True results in a Yes/No field in a query.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

:

You really don't need to care whether a -1 or a 1 is stored, as long as
Access remembers that non-zeros are TRUE. (and yes, Microsoft used "-1" in
Access and "1" in SQL-Server ... go figure?!)

Regards

Jeff Boyce
Microsoft Office/Access MVP
__________________________________________


Thx guys - your replays really helped me.

I am using Access 2003 and MS SQL server 2000.

I added those two fields as bit format and another on the SQL sever as
Timestamp format.

In my form I added those 3 fields manually in the forms properties - record
source.

It is working now but I notice one thing. On the SQL sever it writes 0 or 1
but in Access database view it writes 0 or -1 (minus), but I guess it’s the
same!

 

[Tom Wickerath]

Correction:

I stated below that a TimeStamp was a GUID. That's not correct. In SQL
Server, a timestamp is guaranteed unique only within the database, whereas a
uniqueidentifier (data type) is meant to be unique in the world (i.e.,
globally unique, or GUID).

Kind of confusing now that (in SQL 2008) there is a new data type/alias
called ROWVERSION which replaces (or supplements) the existing timestamp
type. ROWVERSION makes it sound as though it is row (i.e., table) unique,
but it is still defined the same: unique to the database. The name change is
probably a good idea seeing how timestamp (data type) as defined by ANSI SQL
means "date and time", hence why many people think a SQL timestamp is a
date/time.

Thanks to a co-worker of mine who pointed this out to me.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________