Does anyone know if it's possible to do RECORD level rather than
Page level locking using VB and Access formatted databases?
If so, how, or where can I look to find more info?

--Pat

You can simulate record locking by padding your records out to slightly
over 1K.  Which means you will need to add additional fields to your
table (not memo) so that the total length of all fields exceeds 1K.  The
reason this will work is that the Jet engine will not break up records
across pages.  Assuming the 2K page, you will have one record per page.

The sacrifice will be of course disk space and performance.

Paul

I do it all the time, as I write multi-user applications in which page locking
is totally unacceptable.  The basic idea is to:
  1.  Add a boolean field to each table that requires row level locking.
  2.  When a user extracts a record from the database, check the above-
      mentioned boolean field.  If its set, give the user a message that
      the record is being edited by another user.  If its not set, set it and
      let the user edit the record.
  3.  When the user has finished his editing, clear the above-mentioned
      boolean field.
  4.  When your application is started, attempt to open the database first
      in exclusive mode.  If this succeeds, it means noone else is using the
      database, so then clear any of the locking flags that may have been
      left set due to an instance of the application terminating abnormally.
  5.  An finally, do not use bound controls, and perform the edit method to save
      the updated data just before moving the updated data into the edit buffer
      and performing the update method.  In other words, keep the real data-
      base (page) locks open for the shortest time possible.  You won't
      have to worry about record changed or deleted errors (assuming you
      perform operations on tables, as opposed to dynasets), as the above
      record locking scheme makes these errors moot.

You can set up the record locking operations in subroutines which makes the
in-form code very clean.  If you are interested I'd be happy to e-mail you
an example or two.

Richard Schaefer

Nope, can't do it.

Peter Walsh
Research and Development
Land Information Bureau
Department of Environment and Land Management   Phone: 61 (03) 336 815
GPO Box 510E Hobart Tasmania 7001 Australia        Fax: 61 (03) 333 717

To pad the record to 1K, you'll have to use text fields:  all the
other fields are less than 16 bytes, max, but text fields are variable
length.  so maybe 3 fields 255 chars in length, with the default entry
of each set to "XXXXXXXXXXXXXXXXXXXXX" (255 letters).  Good Luck.

------------------------------------------------------------------------------------

MS Access Programming and Networking Solutions
------------------------------------------------------------------------------------

This is a very interesting idea that at first glance seems to be an option for individual
record locking in Access.

But it seems to me that you would still have to use the Edit Method on the record in
order to set the boolean flag.  Once you use Edit, the record is locked on a page basis
(up to 2000 bytes of records).

You can't change the flag without editing the record, so even though it seems that you
are getting single record locking, the records are still locked on a page basis.

If I am missing something, or you have a code example that gets around this problem,
please reply here or by e-mail.

Thanks,

Mike Baker

I beg to differ.

Many of us do it all the time -- what happens is you (the developer) are
responsible for handling the locks through fields. Because of the obvious
problems in record contention, updates, etc. it can get fun at times but
it can be done.

-phil
--
                                "November 5, 1996"
"How can Clinton 'recognize' Vietnam?  It's not like he's ever been there."
    -me / July 6, 1995
 ------------------------------------

A couple of us have been trying to use a sort of pseudo-transaction
processing routing to get around the Access locks - will let you know
how it goes but so far so good.

Pat,
        Record level locking is not possible when using MS Access!!
Only page locking....One thing that you can do is filter out what you
don't want with the FILTER coomand...Maybe down to one record...This
then being Record locking.....:-)!!!!
        Bye...Pls. Re:

Not in a multi-user environment it isn't.

Besides, Page locking isn't *so* bad, once you start using optimisitic
locking and keeping the auto-retries well hidden, it's quite
respectable.

--

If all it takes is an infinite number of monkeys with typewriters,
how come AOL haven't written any Shakespeare yet ?

Well Phill, you may beg to differ - but you are wrong...

MS makes it quite clear in the KBase area of MSDN,
that page level locking is the only option available
to you, however if you use optimistic locking you
have the best chance of achieving **as near as**
record level locks.

Cheers,
Kent Boisen.
************************************
Object Oriented Programming Services
     P.O.Box 69073, Glendene,
      Auckland, New Zealand.
  Ph: (09) 837.6264 or 025.907.012
************************************