Write Conflict When Trying To Update Table

[jo15765]

I have a linked SQL Server 2008 R2 table in my Access 2013 database. When I open the database, nothing opens (no forms, queries etc), and I am the only one in the database. I can run an update query and all fields will be updated as they should be, but if I open the linked table directly and try to type data into one of the rows, I get an error of "This record has been changed by another user"



What setting needs to be changed so I can edit data directly in the table?

[RuralGuy]

If you create a query of that table including all fields, do you still have the issue?

[jo15765]

If youn create a query of that table including all fields, do you still have the issue?

If I run an update query on the table, no I do not have the issue.

[RuralGuy]

No, I meant just a simple select * query and open it and try to change something.

[jo15765]

No, I get the same error if I build a Select query and try to alter any data.

[RuralGuy]

As you suspected there is something not right. Have you tried a Compact and Repair yet? How about *importing* everything into a fresh new db? That usually eliminates any corruption.

[Bulzie]

Not sure matters but when you linked it in did you set the primary key when it asks, if not I don't think it lets you update. Or could be your odbc link, maybe create a fresh one and relink the table.

[RuralGuy]

As I recall, SQL Server likes Primary Keys on tables. Bulzie probably has something there.

[jo15765]

As I recall, SQL Server likes Primary Keys on tables. Bulzie probably has something there.

Yes, I linked it in with a primary key.

I linked it using VBA DSNLESS linked tables

[jo15765]

As you suspected there is something not right. Have you tried a Compact and Repair yet? How about *importing* everything into a fresh new db? That usually eliminates any corruption.

I did a compact and repair, as well as created a fresh database and ONLY linked in this table and have the same result when trying to make modifications to the table

[Bulzie]

In your BE, do you have another table you can link and see if that does same thing. Or create another table, link in and see if same issue. Might pinpoint if issue with your linking or specific to that table, etc.

[RuralGuy]

If it helps, I've asked for some additional SQL Server assistance.

[RuralGuy]

It "feels" like an issue with SQL Server or maybe DSN-Less connections. Here's a link so you might double check your connection:
http://www.accessmvp.com/djsteele/DSNLessLinks.html

[pbaldy]

What's really curious is the error message. I'd expect a permissions or a read-only error depending on circumstances. I've never seen the write conflict error with a table opened directly and nothing else open. When you run the update query, it's an Access query against the linked table, or a pass through query running on the server?

[jo15765]

In your BE, do you have another table you can link and see if that does same thing. Or create another table, link in and see if same issue. Might pinpoint if issue with your linking or specific to that table, etc.

I can link in any other table w/o issue. I also created a fresh copy of this database and linked in about 6 tables, and only ONE is giving this error.