question regarding record locking

[Amit]

MS Access 2000, Windows XP
======================
Hi,

I have a multi-user database application on the server. This database has
many forms (30+).

I was wondering how does the default record locking specified in the Options
--> (Advanced tab) for the database affect the record lock specified in the
form properties?

In a multi-user environment, do I need to specify the Record Locks to
"Edited" for each form in the database even if I have selected "Edited
Records" at the database level?

When I experimented with this, I have "Edited Records" specified in the
Options, and if I have "No Lock" specified in the Form, then 2 users can
simultaneously change the same data/record. I do get the warning message that
the record has been modified by another user. Would you like to save it?
(sorry, I'm typing this from memory, and don't have the exact wording of this
message).
But, I can see that my users will be utterly confused by this message, and
would like to avoid this.

When I specify "Edited Record" for the form, the user who starts to *edit*
the record first (not necessarily the first one to open the record), gets her
way, whereas the second user is unable to make any changes at all, and gets
the Record Lock symbol on the record selector if she tries to edit any
information for the record.

Ideally, I'd like to have a message pop in the above scenario, telling the
user who tried to edit a locked record that the record is currently locked by
another user, and try again after some time.

1. How do I check for the record being locked?
2. In which form event would I put code to check for this (user trying to
edit a record)? On Change?

Will appreciate some light on this issue. Thanks.

-Amit

 

[John Vinson]

MS Access 2000, Windows XP
======================
Hi,

I have a multi-user database application on the server. This database has
many forms (30+).

That's a VERY bad idea right there.

Split the database; the file on the server should be a "backend"
containing *ONLY* the tables. Each user should have their own copy of
the "frontend" containing the Forms, Queries, Reports and so on,
linked to this backend daatabase. This will cure lots of your
concurrency problems right there.

See Tony's FAQ at http://www.granite.ab.ca/access/splitapp/index.htm

for details.


John W. Vinson[MVP]

 

[Amit]

That's a VERY bad idea right there.

Split the database; the file on the server should be a "backend"
containing *ONLY* the tables. Each user should have their own copy of
the "frontend" containing the Forms, Queries, Reports and so on,
linked to this backend daatabase. This will cure lots of your
concurrency problems right there.

See Tony's FAQ at http://www.granite.ab.ca/access/splitapp/index.htm

for details.

Hi John,

Thanks for your response. Maybe I should have mentioned that I have been
exploring splitting the database (before I posted this question), and it will
happen sooner than later. But, I do appreciate bringing that to my attention.

How will the Record Locking be set (both database and individual forms) in a
split database scenario?

-Amit