Can't update a record in a SQL2000 linked table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there,
I have an application with several linked tables of a SQL Server ODBC.
When I try to update one record only in one of tables, MSAccess shows a
message that other user is making changes to the same record and asks me to
copy this record to the clipboard.
I have no problems with the other tables.
The thing is that I'm the only user connected to the server and also I'm the
dbowner.
Any suggestions?
Thanks in advance
 
Sergio said:
Hi there,
I have an application with several linked tables of a SQL Server ODBC.
When I try to update one record only in one of tables, MSAccess shows
a message that other user is making changes to the same record and
asks me to copy this record to the clipboard.
I have no problems with the other tables.
The thing is that I'm the only user connected to the server and also
I'm the dbowner.
Any suggestions?
Thanks in advance
Click to expand...

If you are in a position to make design changes to the table (or can contact
someone who is) try adding a Timestamp field to the table and then refresh
your link. The reason for the problem and the fix is this...

When you edit an ODBC linked SQL Server table Access will try to determine
if the record has been changed by another user or process since you began
your edits. When a Timestamp field exists in the table Access will simply
compare that one field value in your edit buffer to the one on the server (a
Timestamp field is updated any time the record is changed).

If there is no Timestamp field then Access has to compare the value of EVERY
field in your edit buffer to those on the server and with certain DataTypes
rounding incompatibilities or other factors can fool Access into thinking
the record has changed when it really hasn't. The presence of Memo (text)
fields can cause this as can fields that don't map exactly to Access
equivalents (Decimal, BigInteger, etc.).

This can also happen if the Primary Key field on the server table is a
DataType that doesn't map exactly to an Access/Jet DataType so you should
avoid using those types for PKs if you have any say in the matter.
 
Thanks Rick... huge knowledge!

Rick Brandt said:
If you are in a position to make design changes to the table (or can contact
someone who is) try adding a Timestamp field to the table and then refresh
your link. The reason for the problem and the fix is this...

When you edit an ODBC linked SQL Server table Access will try to determine
if the record has been changed by another user or process since you began
your edits. When a Timestamp field exists in the table Access will simply
compare that one field value in your edit buffer to the one on the server (a
Timestamp field is updated any time the record is changed).

If there is no Timestamp field then Access has to compare the value of EVERY
field in your edit buffer to those on the server and with certain DataTypes
rounding incompatibilities or other factors can fool Access into thinking
the record has changed when it really hasn't. The presence of Memo (text)
fields can cause this as can fields that don't map exactly to Access
equivalents (Decimal, BigInteger, etc.).

This can also happen if the Primary Key field on the server table is a
DataType that doesn't map exactly to an Access/Jet DataType so you should
avoid using those types for PKs if you have any say in the matter.
Click to expand...
 
Back
Top