Multi Access Database - Forms

[JaB]

Morning

I have a database that I need to allow multi access to to enter data into
the forms, Im therefore planning to split the database. However, I do have a
few concerns -

1) My primary key is an autonumber, is there any danger of users access the
same record at the same time.

2) I have a query that allows people to call back and edit data, how do I
stop people calling back and accessing the same record at the same time.

Thanks in advance.

 

[bcap]

1) It is possible for users to access the same record at the same time
regardless of the type of your primary key. If your concern is really that
two users might simultaneously try to create records which are assigned the
same autonumber value then no, this can't happen.

2) The quick and dirty way to prevent users accessing the same record at
the same time is to set the form's Record Locks property to "Edited Record".
However, this is a very blunt instrument. What if a user starts changing a
record (thereby locking it) and then goes off for a lunch break leaving
their changes unsaved on the screen? The record remains locked and no-one
can update it (in fact, it's worse than that because not only will the
record be locked but so will any other records on the same data page).

Most people leave the locking behaviour at the default (No Locks). If two
users try to update the same record, the one who gets in second is warned
that another user has updated the record.

 

[Ken Sheridan]

For a brief outline of 'Page-Level Locking vs. Record-Level Locking' see:


http://msdn.microsoft.com/en-us/library/aa189633(office.10).aspx


Support for record locking was introduced in Access 2000, whereas previous
versions used page locking. From then on I believe the default settings in
the Tools | Options | Advanced dialogue were for the use of record locking
along with optimistic locking. This is a perhaps a little contadictory as it
was introduction of support for record locking which made pessimistic locking
a viable strategy, whereas with page locking it could be problematical as it
locked not only the edited record but the other records on the same page.

The Access Developer's Handbook (Ken Getz et al) includes a LockTimeout
class module which can be used to automatically abandon, and thus unlock, an
unsaved record after a prescribed time period where pessimistic locking
(which the ADH recommends) is employed. It does give the user a countdown to
this so they have the opportunity to save the record where a record has been
legitimately locked for a period approaching the specified time limit, rather
than the user having gone to lunch/bed/on vacation while leaving an edited
record unsaved.

Ken Sheridan
Stafford, England