Board index » delphi » How to use the optimistic locking in msaccess 97

How to use the optimistic locking in msaccess 97

Hello,
        I am using the BDE5.0 and msaccess 97, but I found a problem:
        I have 2 workstations running the same application, when station 1 call
the method:
        Table1.Edit, it will not lock the record (in paradox, it will), this makes
the station 2 can also edit the same record, and then when Table1.Post is
called, the BDE will not detect the record has been modified by another
user, how can I prevent it?

thank you very much!

David Ting

 

Re:How to use the optimistic locking in msaccess 97


The BDE has a problem dealing with Access' optimistic locking scheme which
is cause by a problem in Microsoft's DAO. As you have discovered, the native
driver does not give you an error if another user posts a change to the
record you are editing before you post. The only solution I have found is to
save the record in memory (I use a variant array) then reread the record
when I am ready to post and see if it has changed.

--
Bill Todd
(Sorry but TeamB cannot answer questions received via email)
(Remove nospam from my email address to contact me for any other reason)

Re:How to use the optimistic locking in msaccess 97


Quote
Bill Todd (TeamB) wrote in message

<6u4bt8$jn...@forums.borland.com>...

Quote
>The BDE has a problem dealing with Access' optimistic locking
scheme which
>is cause by a problem in Microsoft's DAO.

Below is a note that I picked up somewhere that may apply in this
case.

--
Ernie Deel, EFD Systems
-----------------------------------------------
A computer can solve any problem ...
... except those that just don't add up.

Opportunistic locking on Windows NT fails to resolve caching
conflicts with shared files.  Microsoft has confirmed to us, that
all systems that share files and use NT should set the following
registry entries to FALSE:

Server:
    \HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet
        \Services\LanmanServer\Parameters

        EnableOplocks        REG_DWORD    0 or 1

        Default: 1 (TRUE)

Workstation:
    \HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet
        \Services\LanmanWorkStation\Parameters

        UseOpportunisticLocking        REG_DWORD    0 or 1

        Default: 1 (TRUE)

PLEASE SET THESE VALUES TO 0 (FALSE).  If this key does not exist,
the system assumes a TRUE value for this setting!

Re:How to use the optimistic locking in msaccess 97


Quote
>    I am using the BDE5.0 and msaccess 97, but I found a problem:
>    I have 2 workstations running the same application, when station 1 call
>the method:
>    Table1.Edit, it will not lock the record (in paradox, it will), this makes
>the station 2 can also edit the same record, and then when Table1.Post is
>called, the BDE will not detect the record has been modified by another
>user, how can I prevent it?

Access uses oppertunistic locking which means it does not lock a record when you
edit it like a Paradox table does.  That you do not get modified that the record
has been changed is a short coming of the BDE Access driver.

--
Brian Bushay (TeamB)
Bbus...@DataGuidance.com

Re:How to use the optimistic locking in msaccess 97


Quote
>> I am using the BDE5.0 and msaccess 97
>> will not detect the record modified by another user
Brian Bushay TeamB wrote:
>Access uses opportunistic locking which means it
>does not lock a record when you edit it like a Paradox
>table does.  That you do not get notified that the record
>has been changed is a shortcoming of the BDE Access
>driver.
Bill Todd wrote:
>BDE has problem with Access' optimistic locking
>caused by a problem in Microsoft's DAO
>native driver does not give you an error
>only solution I have found is to save the record
>then reread when I am ready to post

DAO definitely provides the necessary support to detect
records changed by another user.  I don't have sample
code on hand but I carefully verified this some time ago.
You have to open the recordset with the appropriate flags
(as per DAO35.HLP), but after two users edit an optimistically-
locked recordset and modify the field values, the first one to
update succeeds while the second *does* raise an exeption.
The design of the BDE and/or VCL databound controls may
make the native driver's locking problem difficult to resolve,
but it's an Inprise issue *not* a limitation of DAO.

Jet 3.5 isn't perfect but it's one of the most widely used and
thoroughly tested desktop engines on the planet.  Its native
MDB format has transactions, cascading deletes, precompiled
queries, sql optimisation, replication, table/record locking,
security and repair features that {*word*99} all over the BDE's
meager desktop offerings.  Its DAO (and now ADO) classes
aren't perfect either but there are tons of people using them.
The Jet Programmers Guide and DAO35.HLP put the BDE's
scant documentation to shame.
  http://mspress.microsoft.com/prod/books/459.htm

When it began as the Paradox Engine the BDE did a good job.
It was small, fast, well-documented, worked with both Turbo Pascal
and C and had good data types. (It was Paradox compatible too!)
Leveraged to support Interbase it worked ok, and it more or
less survived the addition of extra SQL Links drivers.  But since
Borland attempted cached updates it's had a string of problems
which never seemed to get fixed.  Within a few frantic years the
Paradox and dBASE furniture was shuffled and sold, more drivers
were added, marketing stopped chanting the "upsizing" mantra
(but not before filling the world with TTable-based grids which take
forever to open and have only three-notches on their scrollbars),
and n-tier middleware was acquired and grafted on.  Single tier
database developers today awaken to find their VCL data controls
dependent on an increasingly bloated, buggy and underdocumented
proprietary box which is anything but simple and reliable.

If you want to use the BDE for a local or fileserver database then
change to Paradox 7 format and an early BDE with a proven
track record.
  http://www.inprise.com/devsupport/bde/oldver.html
  http://www.ellipse-data.com/delphifaq/delphi_faq_network.html
Be aware you may have grief if your clients install another product
which uses a different version of the BDE but this is unlikely.  ;)

If you want to use Access 97 then get one of the BDE alternatives.
  http://www.kylecordes.com/bag/index.html

It's hard to understand why Inprise didn't simply buy a product outright
from one of those who already had a mature Delphi/DAO solution.
Instead they evidently reinvented their own from scratch, reproducing
the same bugs that other vendors discovered and fixed ages ago.

I wouldn't rely on a driver that has buggy behaviour on something so
fundamental as multiuser locking!  Sometimes you'll want or need to
implement your own locking scheme, but if not then don't waste your
time writing code to re-read all the fields before you post to disk.
If you need optimistic locking then stay with Access (but use a
replacement driver).  Access does optimistic locking much better
than pessimistic locking because the 2K page issue can be a PITA
if you have records locked for extended periods - especially where
tables are heavily indexed.

Good luck whatever you decide.

Other Threads