SQL Server not updating records, complaining that someone else has record locked. Nobody else is using the db. Help!?

First thing, make sure that all of your tables contain a PrimaryKey and I also add a Row_Version column (sometimes called TimeStamp although not really a time value) column to every table.  This will make it easier for Access / SQL Server to resolve write conflicts.

When upsizing from Access, the SQL Server Migration Assistant will sometimes not add the SSMA_TimeStamp column, but you can force that in the settings.

Dale

Dale,

Thanks for your answer.  However, I did not use the SSMA to convert the tables.  I used a tool called DbConvert.

Are you saying that this field (Row_Version) is mandatory for the SQL Server to identify rows?

Thanks again

No, I'm not saying it is mandatory, but it is HIGHLY recommended.  I would strongly recommend you review your tables and add it.  

There may be something else going on.  Do you have a record open in one form which is open and also in a popup form, this scenario is a common cause of this message when only one user appears to have the db open.

It occurs when you have a dirty record on one form, but have a button or some other code that will open a different form to the same record (maybe a details form).  If the first form is dirty, and the second form gets edited and saved, then the first form will recognize that the data for that record has been changed (ostensibly by someone else, but it was you), and will display this error.

HTH
Dale

Open SQL Server Management Studio (SSMS). -> Open new query window.

EXEC sp_who2

It will show the list of connections with the column "Database Name". It will show who uses (connected to) your database.

It also has column "Blk By" = "Blocked By". It contains the number of the process in the same list which blocks your operation.

is your database in single user mode? 

To add to Dale's correct suggestion, you don't need to do anything other than add a RowVersion column to each table.  You don't need to include it in queries or form recordsets.  It's the easiest way to resolve this problem - which by the way usually stems from floating point field values or Bit fields that contain Nulls.

Guys, thank you for all the input.  However, I can't figure it out.  If I edit the table with SSMC, I can add records, and then modify them with no problem.  

But if edit the table through the front end (MSACCESS), and by this I mean opening the table directly and enter values on the fields, when I try to save the row I get a pop up message that reads:  WRITE CONFLICT.  This record has been changed by another user since you started editing it... yada, yada, yada.  I can add a new row without a problem, but if I immediately modify the contents of any field and try to save the record I get the same WRITE CONFLICT message.

If I do any of this through the SSMC then there are no errors.  So it seems to me that this may be an issue between access and the SQL Server.

I also tried adding the RowVersion field, but this yielded nothing.  The field does not update with any operation I do on the records, either through SSMC or ACCESS.

Could this be a configuration issue, on either the SQL Server or the ACCESS app?

I am pulling my hair out!  Can't figure it out.

After you added the RowVersion columns to the SQL Server table, did you refresh the links on the linked tables in Access?  You must do that for the changes in SQL Server to show up in Access.

Dale

Thank you all for helping.

I had mentioned that Null Bit fields were a possible source of this problem.  In our shop we often use Smallint fields to store true/false values for this reason.  However, I'm still confused, because adding the RowVersion fields should have allowed the Null Bit fields to work normally.  Anyway, I'm glad you got it working.

I'm with Armen, you can use SmallInt and I think you can even set that up in the SSMA data type conversion preferences so that Yes/No in Access converts to SmallInt in SSMA.

BTW, I always set the defaults of Yes/No field types in Access, I never leave the default blank.

Dale