SQL Record locks

I have an access front-end to a SQL server database.

To create a new record the front-end runs an INSERT statement in VBA.  But this statement hangs if another user is editing the table.

I think I need to know more about how record locks work.  Where can I get more information?

LJKMartinTraining DirectorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tomas Helgi JohannssonCommented:

Every modern database has a transactional locking feature.
It's the developers responsebility to implement and code it correctly in the application in respect og it's requirements.
Here is some info on this feature in SQL Server.



Hope this helps.

     Tomas Helgi
Vitor MontalvãoMSSQL Senior EngineerCommented:
As far I remember, MS Access locks a table when in editing mode so shouldn't be a SQL Server behavior but Access.
Can you check in MS Access, in Options, Advanced tab what is the selected option for "Default record locking"?
Also check in the code what's the LockType defined for the ADO Recordset object.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

LJKMartinTraining DirectorAuthor Commented:
Access definitely has a property for record locking, and I was hoping that someone could shed some light on what takes precedence.

The form where the user was editing the record has the Record Locks property set to No Locks, but as soon as he came out the Insert statement started working again.

I only have sight of the database when I'm at the client site, so can't check, but I'm sure it hasn't been changed from the default (No Locks and Open databases by using record-level locking).

I'm actually using DAO, but I'm not creating a recordset in this case, but simply running an Insert statement.  The data source for the form is the same table.  Should I organise things differently?


John TsioumprisSoftware & Systems EngineerCommented:
In many tutorials for Access/SQL you will see a disconnected model....you create a temporal record on the front end and when it is ready is committed to the Server...
Have you remembered to include a timestamp column on your table?
Vitor MontalvãoMSSQL Senior EngineerCommented:
LJKMartin, do you still need help with this question?
LJKMartinTraining DirectorAuthor Commented:
Hi, there were a number of useful comments and the links explained how locks work in an earlier version of sql server, but nothing about how to control it, so I'm not much further forward.

I not sure i understand what john says is a temporal record.  a temporary record wouldn't do what the client wants (they are freaked out by the fact that the ID isn't generated first), so I have to insert the record and then display it.

The diagnosis of the problem hasn't reached a conclusion - although it doesn't seem to be locking after all.  Also, it hasn't happened for a couple of weeks now.

I appreciate all the suggestions, but should close the question.

Dale FyeOwner, Developing Solutions LLCCommented:
If you are using SQL Server as your backend, it will not generate an identity value until the record is saved.  The problem with this is that if the user then decides to cancel the entry, they will not be able to generate that same identity value again.

If you have a field that you want the customer to see, it should not be an identity (autonumber) field.  You should generate it yourself.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.