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?
Cheers
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?
Cheers
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
LJKMartin, do you still need help with this question?
ASKER
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.
Cheers
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.
Cheers
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.
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.
ASKER
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?
Cheers
LJM