Avatar of Samantha Moore
Samantha Moore
Flag for United States of America asked on

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

Converted a database from an MS Access Jet to an SQL Server.  Front end is Access.  When editing a record, some tables cause the server to complain that somebody else has locked the record and I can't update it, when I am the only one logged in to the server.  Inserting records into certain tables causes another error stating that the record could not be read back, so I can't retrieve the primary key (identity column) of the record inserted to modify it.

This happens both when using MS Access to insert/modify records, or even the SSMC (SQL Server Management Console), so I don't think it is an MS Access issue.  Anybody have any insights?
Microsoft AccessMicrosoft SQL Server

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
Dale Fye

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
Samantha Moore

ASKER
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
Dale Fye

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



Your help has saved me hundreds of hours of internet surfing.
fblack61
Andrei Fomitchev

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.
David Johnson, CD

is your database in single user mode? 
Armen Stein - Microsoft Access MVP since 2006

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Samantha Moore

ASKER
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.
Dale Fye

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
ASKER CERTIFIED SOLUTION
Samantha Moore

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Samantha Moore

ASKER
Thank you all for helping.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Armen Stein - Microsoft Access MVP since 2006

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.
Dale Fye

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