"This record has been changed by another user since you starting editing it" (1 Viewer)

[RogerCooper]

Last night, I needed to rebuild my database by creating a new database and copying all objects into the new database. I am now receiving the "This record has been changed by another user since you starting editing it" error when I edit a specific table that is linked to SQL Server.

1. If I go back into the old database, I can edit the table.
2. If I create a Select query, I can make changes using the recordset.
3. The SQL table does contain bit fields, but they have default values set.
4. If I copy and paste the table from the old database into the new database, it does not work.
5. I have tried deleting the link and recreating it.

I had to set some options in the new database, but I don't see that any would affect editing SQL tables. I can work around this issue, but I would still like to know what is causing the problem. Any ideas?

 

[isladogs]

Perhaps there are some null bit field values from before you set the default values. See my article

Write Conflict Errors

This article explores some of the causes of the 'dreaded' write conflict error and how to deal with those issues.

www.isladogs.co.uk

 

[Minty]

Does Access recognise the primary key in the table design?
If not, it would stop you from editing it, although the select query being editable is odd. Did you bring in all the fields?

There is another strangeness I came across a while ago that resulted in that error, but my memory is failing me at the moment.
I'll post back if the second coffee of the morning wakes me up a bit more.

 

[RogerCooper]

Access is recognizing the primary key. It works as long as I select specific columns but fails if I use the "*".

 

[Pat Hartman]

Access is recognizing the primary key. It works as long as I select specific columns but fails if I use the "*".

Then that tells you that there is some column with a problem.

Create a query that specifically selects all columns. You should get the error. Then you can back out one column at a time to find the issue.

Also, using select * is never recommended. It is always best to select the specific columns you need as well as including a where clause to limit the rows.

 

[RogerCooper]

Just as you thought, there was a field that still had null values, even though the default was set. Fixing that resolved the issue.

 

[Pat Hartman]

Glad you found it.

 

[isladogs]

Just as you thought, there was a field that still had null values, even though the default was set. Fixing that resolved the issue.

I thought that would be the case as I had exactly the same experience after first converting to SQL Server almost 20 years ago!
At thae time, it took me ages to pin down the cause. Some things you never forget!