Advice required on record locking please

[Stuartp]

I am trying to understand how best to implement record locking on a program that may potentially be used by several people accessing a central database. I've spent a few hours reading around about record locking, so here is what I want to achieve and what I've implemented so far from what I've read:

The program will be to allow people to store, retrieve, edit and print quotations from a central database. The database might be either SQL Server 2000 or Access 2000. If a user opens a quotation I do not want any other user to be able to open it until the user currently in it closes it again.

To start with then I open a connection:

 conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\dbtest\data.mdb.mdb;Persist Security Info=False"

for Access, or for SQL Server I use

 conn.open "Driver={SQL Server};Server=Aron1;Database=pubs;Uid=sa;Pwd=asdasd;"

Then I want to open a quote, so I use

 rs.open "SELECT qtHeader.orderno, qtHeader.customer, qtLines.PartNo
FROM qtHeader INNER JOIN qtLines ON qtHeader.orderno = qtLines.OrderNo
WHERE (((qtHeader.orderno)=123456));", conn, adOpenKeyset, adLockPessimistic

From what I have read I understand that by using a pessimistic lock, this will lock the record as soon as it "opened for editing" until it is updated. However I was confused over how to open for editing. DAO had an rs.edit, but ADO does not.

The only way I have found to do this is to open the recordset and then immediately change one of the columns, e.g. a last accessed date. This does have the desired effect in that if another user tries to open the same quote it gives an error which can be trapped, but I am wondering if this is the best way to go about it.

Could someone offer some advice please?

Many thanks

Stuart

 

[TallOne]

HI Stuart,

I'll try to help ya. This is from how I understand it and anyone can jump in to save me at any point.

>If a user opens a quotation I do not want any other user to be able to open it until the user currently in it closes it again.

When users open a quote they are just reading a record. I'm not sure you really want to resrict read access. Doesn't sound like a good stategy. Maybe another member could elaborate on this a little.

>However I was confused over how to open for editing

Unlike DAO & RDO, ADO doesn't support the Edit method. By simply assigning a new value to a field, your in edit mode until you issue an update OR move to another record in the recordset. How others can act upon that record depends on the locktype. In your case your using pessimistic locking. I think this means that while a record is in edit mode by user A, if user B enters edit(which is an attempt to change a value) an error will occur something like "Can't Update, Record locked by another user" This seems to be the place where you would trap the error and tell user B to try again later. There are alot of different situations(i.e. BatchUpdate voids anything I've said) that change all the rules and I couldn't begin to elaborate as I'm new myself. For the most efficient Recordsets use ADO's default forward-only, read-only, non-cursors and do all your operations using SQL Statements or Stored Procedures. That's the strategy I'm currently developing. Hope this helps.

Jerry


The more knowledge I acquire, the more knowledgeable I am about my ignorance.

 

[Stuartp]

Hi Jerry, thanks for your comments.

From the testing that I did, as I am using pessimistic record locking, as soon as the record goes into edit mode, i.e. as soon as I change a value, another user cannot even access the record until it is updated, or the user moves to another record.

I was just wondering if immediately changing a value in the recordset in order to make it "edited" was the best, or only, way to do it. It does sound like it is.

Why do you say restricting read access doesn't sound like a good strategy? I was planning to have it working this way because I've seen commercial software do the exact same thing - if one user is in a quote any other users attempting to access it are told it is in use and they cannot access it. Main thing is I don't want two people to be able to edit at the same time, or to have one person over write the changes another person made because they didn't know someone else was using it.

Thanks

Stuart

 

[chiph]

Why do you say restricting read access doesn't sound like a good strategy?

Because you then have to write separate code to unlock the record (or reboot the server, etc) when:
1. A user reboots their PC while in the middle of an edit
2. A PC locks up in the middle of an edit
3. A user goes to lunch/on vacation/is fired while they're editing a record and others need to change that same record.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first