Locking records

Hallo

How can I lock records while using ADO? We tried every combination of Cursortypes, locktypes and still 2 users can edited the same record simultationly!!!

Thanx

[666539]

What database are you writing to. The type of locking you have available to you depends on the cursor type you use. Various databases have different cursortypes available to them, but you wouldn't know it if you didn't read it. For example, with cursor locations, you could tell access to use a serverside cursor in the code, you wouldn't get an error, it would just assume you didn't know what you were doing and default you back to a client side cursor.

I have a book called "Hitchhikers's Guede to Visual Basic and SQL Server" by Bill Vaughn. It covers a lot of that stuff.

The type of locking available depends on the cursor type and location, which depends on the type of database being used. There are a lot of variables to consider.

I am using Access 97 database

[666539]

I've looked up some information. I've found that client side cursors don't support pessemistic locking. I'm pretty sure that Access databases don't support server side cursors.

So with an Access database you will be limited to client side cursors, which will limit you to optimistic locking and will NOT allow you to use dynamic or keyset cursor types.

Hope this helps.

Yes that's right. We are using that settings, but it is not enforced by the Adodc. Two users can still edited the sam record simultaneously. I don't understand it

[666539]

You said that "it isn't enforced by adodc". What part specifically isn't being enforced.

Which settings are you using specifically?
What is the CursorLocation?
What is the CursorType?
What is the LockingType?

So with optimistic locking 2 people can edit the same record at the same time, but if one saves the record the second will get an error if they try to save the same record.

When 2 people are editing the records, are they editing the same fields? I've noticed that with SQL Server and ADO (could work with access too but I'm not sure) that if if user 1 changes field A and saves then user 2 can change field B and save with no conflict.

We ran a test scenario. Both users open the same file. One user makes changes and saves it. The second user is still seeing the old data, changes it and overwrite the changes made by user one (when both save at the same time, well the one that is lucky will have there data saved). As far as I understand concurrency control, user 2 must not be able to edit the record because it is locked by the first user. No error message is given, no warning...nothing.

CursorLocation: adUseClient
Cursortype: adOpenStatic
Locktype: adLockOptimistic

[jas_alien2]

why dont u put another field with a boolean value if user A will open that record, user will trigger the field to TRUE, if user B try to open that same record and that field is = TRUE then user B cannot edit that record. and vice versa.

[Destined Soul]

I apologize for bringing up such an old thread, but it contained some useful information..

My question regarding 666539's comment is if it still holds true for all versions of Access?

666539 stated:
I've looked up some information. I've found that client side cursors don't support pessemistic locking. I'm pretty sure that Access databases don't support server side cursors.

So with an Access database you will be limited to client side cursors, which will limit you to optimistic locking and will NOT allow you to use dynamic or keyset cursor types.

Since I'm stuck with Access 97 (though I could use 2000 if need be), I was also wondering if putting in a field for if the record is locked or not was still the main way to get around this method?

(I am concerned if user's program somehow doesn't set the field to unlocked.. the only way I can think of for this is to use a expiry date/time and check to see if that has expired. A problem with this is if each users' date/time is off by more than a few minutes.. then they could end up resetting the value even if the timer still has some time left on it.)

Any suggestions? (Other than using another database, which is what I'm really considering to do..)

Destined

[666539]

I wrote this message several years ago, and although my programming focus has shifted from VB a bit, I can comment on how I have seen things like this done since I wrote this message.

I have seen applicatons built with classes to represent the data. The class has a method to retrieve, save and delete the data etc. Data is edited and delted with SQL calls to the database rather than keeping a cursor open. This will eliminate the problem of concurent users locking each other out.

There is a company out there called Shoutsoft inc. I think their web site is www.shoutsoft.com. They sell a software product called com express. It is a wizard driven app that will build the type of object oriented application I'm describing. Their most recent release will build some VERY COMPLEX code, so if you're not familar with VB object programming you may benefit from buying a book about it.

VB also ships with a data object wizard that will build similar classes to what the shoutsoft product builds, but the Microsoft products are MUCH easier to understand. You can get to the data object wizard under the same menu as the project & deployment wizard.

Going the class rought is a little more complex until you get used to it, but it adds much more flexability to the code.

I hope this points you in the right direction.

[Destined Soul]

Hey 666539,

Just a side note about your comment earlier.. it turns out that Access (2000?) does support server side cursors. I'm not too sure about 97, but I would take a guess that it does, since the article I read references Jet 4.0 (which is what I'm using.)

Here's the link:
http://msdn.microsoft.com/library/de...jectbasics.asp

Just thought I'd at least attach this note to this thread. I haven't tested if it a server side cursor does work with Access97, as I'm too lazy.

Destined