multi user

J

Joel Allen

Hi,

Is it possible for 2 people to be in a record at the same time and see if
the other person makes changes instantly?

I'm pretty well versed in Access, but have never run into a need for that
until now!

Thanks for your help,
Joel
 
6

'69 Camaro

Hi, Joel.
Is it possible for 2 people to be in a record at the same time and see if
the other person makes changes instantly?
Click to expand...

Not unless both users intend to edit the same record, but with pessimistic
record locking, yes. (If the second user is only reading the record,
there's no notification that the record has changed or is being changed.
The user only knows of changes if the record source is requeried and the
user notices that it's now different.) The second user will be blocked from
editing the record, as soon as the first user starts editing the record.

With optimistic record locking, the second user who attempts to update the
changed record will receive an error message at the time of attempted
update, telling him that the record has changed. The second user's changes
will be discarded.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
 
B

BruceM

Could you explain a bit just what is meant by optimistic and pessimistic
locking (or direct me to more information on the subject)? How would one
set these options? I expect it is in Tools > Options > Advanced, but which
option is which type of record locking? In what circumstances would one be
preferable to another?
 
6

'69 Camaro

Hi, Bruce.
Could you explain a bit just what is meant by optimistic and pessimistic
locking
Click to expand...

Optimistic locking means that the record isn't locked until it's actually
updated, whereas pessimistic locking means that the record is locked as soon
as an edit starts, and remains locked up until the record is eventually
updated (or the edit is cancelled). Optimistic locking provides for very
brief record locks, while pessimistic locking could take quite a while,
especially when a user leaves on a lunch break in the middle of editing a
record. ;-)
How would one set these options? I expect it is in Tools > Options >
Advanced
Click to expand...

Yes. It can also be set on individual forms on the Properties sheet, or in
VBA with DAO or ADO Recordsets.
which option is which type of record locking?
Click to expand...

Excellent question! In the "Default Record Locking" section of the dialog
window:

"No locks" means optimistic record locking (no locks until the record is
finally updated);

"All records" means a full table lock (no other record in the table can be
edited);

"Edited record" means pessimistic record locking (locked at the very
beginning of the record edit);
In what circumstances would one be preferable to another?
Click to expand...

Optimistic record locking is for a fast, heavy load of transactions, where
the user can rekey the discarded edits if the update is blocked due to
another user modifying the record before the current user finished editing.
Pessimistic record locking is generally for a lighter load of transactions,
in cases where it would be highly inconvenient (or impossible) for the user
to rekey the data if the record edit was blocked.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
 
T

Tony Toews [MVP]

Optimistic locking means that the record isn't locked until it's actually
updated, whereas pessimistic locking means that the record is locked as soon
as an edit starts, and remains locked up until the record is eventually
updated (or the edit is cancelled). Optimistic locking provides for very
brief record locks, while pessimistic locking could take quite a while,
especially when a user leaves on a lunch break in the middle of editing a
record. ;-)
Click to expand...

Now isn't that really page locking a lot of the time instead of just
record locking?

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
B

BruceM

Thanks, Gunny. I have seen the term used from time to time, and I think I
am at a point where I can begin to think about some of the subtleties such
as this. Your explanation was very clear, and answered my questions fully.
I can see that in complex records with memo fields and so forth it would be
a hassle at best if an update was blocked after spending a lot of time
keying it in (similar in scope to the inconvenience of composing a detailed
e-mail only to have the server take time off). I suppose information
entered while speaking with a customer on the phone, or something like that,
would be in the same category of data that would be difficult or impossible
to recapture.
 
6

'69 Camaro

Hi, Tony.
Now isn't that really page locking a lot of the time instead of just
record locking?
Click to expand...

Good point. If the back end is Jet 3.5, yes. Or if the back end is Jet 4.0
and the developer doesn't know how to avoid page-level locking, yes. And
since there are so many choices of database engines for back ends, one
needn't limit oneself to Jet's default page-level locking just because it's
the easiest choice when building a database.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
 
6

'69 Camaro

Hi, Bruce.

You're welcome.
I suppose information entered while speaking with a customer on the phone,
or something like that, would be in the same category of data that would
be difficult or impossible to recapture.
Click to expand...

That's my thinking, too, but if the number of times I've had to repeat
personal information to the customer service representative on the phone
("Oops. The computer didn't like that. Can you give me all of that
information again, please?") is any indication, none of the database
developers have ever considered that when building their customer service
database applications. ;-)

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
 
T

Tony Toews [MVP]

Good point. If the back end is Jet 3.5, yes. Or if the back end is Jet 4.0
and the developer doesn't know how to avoid page-level locking, yes. And
since there are so many choices of database engines for back ends, one
needn't limit oneself to Jet's default page-level locking just because it's
the easiest choice when building a database.
Click to expand...

But there are a number of rules about when page vs record locking is
used. Such as opening an MDB by using the file extension or using the
msaccess.exe and opening the MDB. Weird stuff like that.

I don't remember them all right now. Just vaguely recall some
postings.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
6

'69 Camaro

Hi, Tony.
But there are a number of rules about when page vs record locking is
used. Such as opening an MDB by using the file extension or using the
msaccess.exe and opening the MDB. Weird stuff like that.

I don't remember them all right now. Just vaguely recall some
postings.
Click to expand...

Yes. Weird stuff like that! The default when opening the database is to
use page-level locking, because that's DAO's modus operandi. The way to get
around it is to use ADO to connect to the database file with row-level
locking, then open a second connection to the database. Even if it's a DAO
connection, it will still use the same locking level as the first connection
to the database file. For more information, please see the following Web
page in the Microsoft Knowledge Base:

http://support.microsoft.com/kb/306435/en-us

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
 
T

Tony Toews [MVP]

"'69 Camaro" <[email protected]_SPAM>
wrote:

Yes. Weird stuff like that! The default when opening the database is to
use page-level locking, because that's DAO's modus operandi. The way to get
around it is to use ADO to connect to the database file with row-level
locking, then open a second connection to the database. Even if it's a DAO
connection, it will still use the same locking level as the first connection
to the database file. For more information, please see the following Web
page in the Microsoft Knowledge Base:

http://support.microsoft.com/kb/306435/en-us
Click to expand...

Ah, very interesting. Thanks to you and Tom for that one.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Top