Linked sql server table not allowing update

[cmmrfrds]

I have a linked sql server table along with many other linked sql server tables. It is an ODBC link. I checked the primary key - Okay. The unique index - Okay. I can update the field in the table in Enterprise Manager.

When I try to update a field in this particular table, I get the error that another user is editing the record. Initially on my Form but the same thing directly in the linked table. I dropped and relinked the table. I am puzzled. Does anybody have an idea of what maybe happening?

 

[cmmrfrds]

If figured it out. The problem was a bit field in sql server.

 

[cmmrfrds]

A little more background on the ODBC updating problem for anybody interested. The problem was Access/odbc not translating a bit field in sql server correctly. I changed the bit field to a char data type of 1 byte and the table updates fine.

The problem is a combination of Access and ODBC and the way it transalated the bit data type from sql server. ODBC is similiar to ADO when it trys to figure out if the record you are editing has already been changed by another user. In this case, it compared all the prior update field values in the record to the values in sql server and found a difference. The difference was only due to not transalating the bit field correctly, therefore, it determined another user had changed the record which was not the case. My solution was to change the bit field to character so that odbc could translated correctly. Another solution which I did not try would be to put a timestamp field in the sql server table this way since a timestamp is always unique odbc would use the timestamp instead of each fields values to check for a change and everything should be good.

 

[surzycki]

Hello,

I am having the same problem but, I am afraid I don't quite understand your explanation. FOr me the problem is identicle except for that I am linking two SQL servers. I have found that the Update doesn't work when the two servers are SQL 7. HOWEVER, when the servers are both SQL 2000 everything works fine, also, when a SQL 2000 does the update on a SQL 7, things work, but vice versa 7->2000... nada. Do you think this is the sam problem?

Stefan

 

[cmmrfrds]

When you say the update does not work. Is this from an Access mdb that uses ODBC to link to tables on both servers. Do you have a problem if you take the same sql statement and run it in Query Analyzer? If it works from Query Analyzer then the problem is related to the Access program but could also be in the ODBC link.

You probably need to post your question in the sql server forum.