How can I lock just one record in DAO? 1

[Sprowler]

I am using a DAO data control to populate a form from an Access97 db. My prob is when a record in the Recordset is being edited, other records are locked too. It appears I am in Page-Locking mode, but I don't know how to set the Data Control's Locking to Record-Locking rather than Page-Locking.

Please help coz this is driving me and my users wild.

 

[stoolpigeon]

There is a thorough explanation of your options with examples at

http://www.microsoft.com/accessdev/articles/bapp97/chapters/ba10_4.htm

(This is a long page- what you are interested in is a ways down, but you may find it helpful to read the whole thing)

Access 97 does not support locking down to the level of the record. You can get as fine as the page your data is on. This is finer than a table lock though. Using optimistic locking should help there.

 

[BBaggins]

Consider implementing your own row-locking scheme. Add a boolean field to the table that indicates a row is locked, and put the read/write permission logic in your application code.

Don't use a dynaset-type recordset when editing data - use a forward-only snapshot-type recordset, then build a query to update the data via the DAO/ADO .execute method. You can probably do away with the data control altogether.

Homebrew locking can have other benefits, such as being able to not only lock a row, but to lock dependent data as well.

Caveat: this approach only works if your application is the only one that modifies the data.

 

[CCLINT]

>Caveat: this approach only works if your application is the only one that modifies the data.

So why use it at all?
(Or is it too early in the morning for me.)

I definately would not recommend using a boolean flag to the record.
You are just opening a can of worms here.

Why not start migrating to ADO, changing your apps UI one at a time until all have neen changed?

Under DAO, you only have page locking. If you absolutly need record locking, then I would use a seperate table, even in a seperate db, and have fields to identify the table, record being locked (will need a unique key in the table being locked), user ID, and time locked.

But what if the application or system crashes? The locking information remains in the locking table, and other users still cannot edit that record even after the first user has gone home.

If the application or system crashes, then the ldb file will reflect if the user is still active or not.

So, you would need to check the ldb file, and if a user is not active anymore, turn over the record locking to the user who is attempting to place a new lock on the record.

But what if the user who was logged in before restarts? The record is even locked for them, and if this user decides not to go back to the same record, the record continues to remain locked for others, even with the above technique, because the user who originally locked the record, is active again.

When a user who locked a record, but got thrown out, because of a system crash or whatever, and logs back in, the first thing done is the locking roster table is cleared of all entries under that user's name.

You may want to consider additionally adding the workplace ID along with the user's name.

Now that this problem is solved (crashes or incorrect application/system shutdowns) you can carry on to your home made locking system under DAO a little more effectively....

 

[Sprowler]

Thanks for your reply guys. Much appreciated.

CCLINT? Do you mean that ADO can record-lock instead of page-lock? Can you give a brief example code or point me to an article? I am very new to ADO.

Your help is muh obliged.

 

[CCLINT]

Locking causes alot of overhead and can cause other problems, as well as user frustration.

Let me ask you this first:
Do you really need record locking at all?
Why not use a TimeStamp and, when saving record changes, if the record has changed since last retreived, inform the user and decide whether to overwrite the changes or get a fresh view and edit again.

I see it this way: Why lock a record when user "A" is editing, if user "B" can sooner or later edit those changes save by user "A" anyways?
Why not just inform the user, show the changed record in a new window, if needed, and let them decide form there?
This, determining whether a record has changed since displayed, can be done with a timestamp - even more effectivly on SQL Server because the check and update can be done at the same time - instantaneously - as opposed to using an extra query!

 

[Sprowler]

I want to record-lock, because 2 people administer the db and could open the same record or one nearby. If it's the latter, under page-locking they can't do it which is frustrating. It won't happen too often, so I don't want to get too complex.

Thanx for your reply.

 

[CCLINT]

For a record lock you will need to use ADO 2.5 and the JET 4 provider, as well as a JET 4 database (ACCESS 2000 mdb)

The setting will apply to the whole connection and needs to be set after the provider property is set, and before the connection is opened:

With conn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Jet OLEDBatabase Locking Mode&quot = 1
'...set other properties here

.Open "C:\myData.Mdb"
End With

Then, when you open the recordset, use adLockPessimistic as the LockType.

 

[Sprowler]

Thanks for your advice CCLINT, I'll try it tonight. Enjoy your weekend.

 

[Sprowler]

I'm working hard on changing my db to using ADO. Yet to test the record locking bit, but (so far) things are lloking good.

I'll update you when I've tested it properly.

Thanx again for your time.

 

[Sprowler]

OOPS! Sorry CCLINT. My last post was for you. Deep apologies.

 

[Sprowler]

Message for CCLINT (and anyone else).

I have converted all my DAOs to ADO (phew!!!) and all seems OK after testing, except when 2 users edit the same record I get this error.

"Run-time error '-2147217887 (80040e21)
Multiple-step operation generated errors. Check eah status value."

Any ideas how I can trap this, and exactly what is happening when it occurs?

Much obliged for your help.

 

[Sprowler]

Message for CCLINT (and the others who replied to me - thanks guys!!)

I tried to record lock, but got in a real pickle with it. So I've taken your advice and used a timestamp instead.

Now I have 2 extra fields in my db, which hold the username and Now() values in the 2 fields (LastEditBy and LastEditWhen) when changes are saved. I trap the date/time each user selected edit, and when they hit the save button my pgm checks the new fields in my db, so if the date/time of the last edit is >= to the time the new user selected edit, I simply msgbox them to warn them, refresh the record so they can see what was changed, and if they want to do their edit, they can choose edit again.

May seem a bit round about, but it appears to work really well.

Thanx for your help CCLINT (and you other guys), I'll give you a star for your good advice.

 

[CCLINT]

Thank you.
That's the way I see best.
Also, you might want to inform the user only if the LastEditBy is not the same name as the current user saving the record.
A TimeStamp with JET dbms is only good up till one second. If two users save almost at the same time, then the TimeStamp may not be accurate enough.
Checking the LastEditBy will give the extra uniqueness needed for this check.

You might consider disconnecting your recordset from the connection (client side cursor), doing your updates via a Query, or SP, and just checking the time stamp this way.

 

[Sprowler]

Good idea CCLINT. I'll try it. I have been thinking of moving all my Access Dbs up to SQL Server using MSDE. Is this easy? Do I need SQL Server or will the MSDE allow me to upsize via Access2000? I will of course check this site and Microsoft, but, as always, your advice is very much valued and appreciated.

Thanks again.