Link to home
Create AccountLog in
Avatar of Samantha Moore
Samantha MooreFlag 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?
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

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
Avatar of 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
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
ASKER CERTIFIED SOLUTION
Avatar of Samantha Moore
Samantha Moore
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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