I have a fairly large VB5 application and I have tried to implement my own
locking scheme using a lock table. This works fine most of the time but
occasionally I am getting one or two unexpected errors when trying to update
records.

My record sizes are all pretty small (less than 2K).

I have some questions:

1. It looks like I should be setting the LOCKEDITS parameter to false when I am
opening my dynasets, i.e. it defaults to pessimistic locking. Is my assumption
correct? If I am using lockedits=false, does Jet still lock pages when new
records are being inserted - ie could I still get a lock error when 2
workstations try to do an Addnew at the same time?

2. I use BeginTrans..CommitTrans statements in various places in the app,
mainly on sensitive areas (writing to one table usually), to ensure that I can
abort the whole update operation if an error occurs in the middle. I have seen
somewhere that using transactions will cause pessimistic locking to come into
force. Is this true? If so, I will not be able to use transactions with my
optimistic locking scheme.

3. Would using ADO make things easier? How much work is involved in changing
over to ADO from DAO? Can it be done in VB5 or would I need to convert the
whole project to VB6?

Any advice appreciated!

Andy Rigby

You should definitely use optimistic locking.  Using DAO, you should open
your recordsets like this:

    sql = "Select * From SalesStaff Order by Name"
    Set rsSalesStaff = dbStaff.OpenRecordset(sql, dbOpenDynaset,
dbSeeChanges, dbOptimistic)

Both dbSeeChanges and dbOptimistic need to be used for multiuser access.

Jet locks the 2k page where a record is being added with AddNew.  But with
optimistic locking the lock only occurs when the record is actually written
to the database with the Update method. (Just like with edits.)

Remember that using dbSeeChanges and dbOptimistic generally requires you to
handle the database errors that will occur in a multiuser environment.  You
need to trap and handle error number 3197 - Data has changed and error
numbers 3186 and 3260 - Record locked by another user.  These errors will
occur when you try to update a record that has been changed by another user
or is locked by another user.

I've never heard anything about this.  I always use optimistic locking,
whether or not I am using transactions.

Changing from DAO to ADO is a good bit of work.  I don't think ADO makes
this kind of thing any easier than DAO.

You can download ADO from MS and run it under VB5 just fine.  You definitely
do not need VB6 to use ADO.

Hope this helps,

Kerry Moorman

kerry

Thanks for your advice.

So therefore even if I am maintaining my own lock table of recrods currently
locked (so nobody else can change those records), I could get a lock conflict
if 2 users issue an Addnew-Update at the same time? And also if 2 user update 2
different existing records that happen to be in the same page?

Is this a correct statement?

Andy

Surely with row-level locking in ADO, this would be simpler - use pessimistic
locks, then if 2 users issue an 'Edit' on the same record, the second will get
a trappable error. And presumable there can be never be a lock conflict if 2
users do an Addnew..Update at the same time, because 2 different (new) rows
will get locked??

That's definitely true for 2 records in the same page getting updated.  I
think its true for the Addnew/Updates, but I'm not sure about that.

Can you do row-level locking on an Access .mdb database with ADO?  I wasn't
aware of that.  Is that something new with Access2000?

But you can handle lock conflicts with DAO and Access9x.  Using dbSeeChanges
and dbOptimistic when opening a recordset will allow you to trap and handle
locked record and data has changed errors for Access databases.  Why is it
that you need your own lock table?

Kerry Moorman

Because I want to tell people that the record is locked BEFORE they start
editing a record on screen. So I look in the lock table and see if this record
is in there because somebody else is editing it. Then they won't waste time
typing lots of changes only to find out that they need to abandon them.
 I was hoping that with optimistic locking and this separate lock table, I
would not need any error trapping at all (apart from critical errors).