how the data being shared (1 Viewer)

hfsitumo2001

Member
Local time
Today, 05:41
Joined
Jan 17, 2021
Messages
365
Hello, I want to know how the data being shared in the database. if UserA is using table customer just to view customers, While user B is using input form for supplies usage which form has inventory table linked with customer table. Can both user do their jobs at the same time without any problem?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:41
Joined
Oct 29, 2018
Messages
21,358
If you properly split the db, then multiple users should be able to safely do their jobs.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:41
Joined
Feb 19, 2002
Messages
42,981
In the rare case where user1 opens a form to record x and starts changing it but before he saves, user2 opens a form to the same record and starts changing it:
1. If your form shows record selectors, user1's record selector will change from a right triangle to a pencil as soon as he starts editing the record . When user2 opens the form, the record selector will show a circle with a line though it indicating that someone is actively editing the record so the users can get visual clues.
2. If user1 saves first, he gets no error message.
3. When user 2 saves, he gets a confusing error message with 3 options.
4. If user2 has attempted to save first, the message user1 gets is determined by what option user 2 chose.

To see how this all works, put two computers side by side and open the app from each. Please use a test database to conduct the experiment. Then see what happens and what error messages the users get. If this type of "collision" is likely to be common for your users, you should probably add code to trap the error and make it more understandable or make a decision as to what should happen and tell the user the result.

I haven't had any apps where this was ever a problem so I just let the users get the message and tell them how to handle it.
 

hfsitumo2001

Member
Local time
Today, 05:41
Joined
Jan 17, 2021
Messages
365
In the rare case where user1 opens a form to record x and starts changing it but before he saves, user2 opens a form to the same record and starts changing it:
1. If your form shows record selectors, user1's record selector will change from a right triangle to a pencil as soon as he starts editing the record . When user2 opens the form, the record selector will show a circle with a line though it indicating that someone is actively editing the record so the users can get visual clues.
2. If user1 saves first, he gets no error message.
3. When user 2 saves, he gets a confusing error message with 3 options.
4. If user2 has attempted to save first, the message user1 gets is determined by what option user 2 chose.

To see how this all works, put two computers side by side and open the app from each. Please use a test database to conduct the experiment. Then see what happens and what error messages the users get. If this type of "collision" is likely to be common for your users, you should probably add code to trap the error and make it more understandable or make a decision as to what should happen and tell the user the result.

I haven't had any apps where this was ever a problem so I just let the users get the message and tell them how to handle it.
Thank you Pat, I will test later, coz right now, I do not even put yet the backend in server, waiting for the IT people to give us access to server.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:41
Joined
Feb 28, 2001
Messages
27,001
The only "gotcha" in all of this is that when creating new records from two places at once, you could get into a sticky situation if you were careless with your lock specifications. Using Optimistic Locks or No Locks, you would get minimum interference between your two hypothetical users. Using Pessimistic Locks, you would be more confident that users could not overwrite each other's data - but you would risk other errors caused by write conflicts. It's kind of a trade-off.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 23:41
Joined
Jan 20, 2009
Messages
12,849
Note the answers above assume that bound forms are being used.
Anything can happen with unbound forms writing to the tables using SQL commands.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 12:41
Joined
Sep 12, 2006
Messages
15,614
In the rare case where user1 opens a form to record x and starts changing it but before he saves, user2 opens a form to the same record and starts changing it:
1. If your form shows record selectors, user1's record selector will change from a right triangle to a pencil as soon as he starts editing the record . When user2 opens the form, the record selector will show a circle with a line though it indicating that someone is actively editing the record so the users can get visual clues.
2. If user1 saves first, he gets no error message.
3. When user 2 saves, he gets a confusing error message with 3 options.
4. If user2 has attempted to save first, the message user1 gets is determined by what option user 2 chose.

To see how this all works, put two computers side by side and open the app from each. Please use a test database to conduct the experiment. Then see what happens and what error messages the users get. If this type of "collision" is likely to be common for your users, you should probably add code to trap the error and make it more understandable or make a decision as to what should happen and tell the user the result.

I haven't had any apps where this was ever a problem so I just let the users get the message and tell them how to handle it.

I assumed you only got the locked record indicator if you were using a pessimistic locking strategy. Am I right, or does it always show the indicator even if you are not locking records?
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 12:41
Joined
Sep 12, 2006
Messages
15,614
The only "gotcha" in all of this is that when creating new records from two places at once, you could get into a sticky situation if you were careless with your lock specifications. Using Optimistic Locks or No Locks, you would get minimum interference between your two hypothetical users. Using Pessimistic Locks, you would be more confident that users could not overwrite each other's data - but you would risk other errors caused by write conflicts. It's kind of a trade-off.

I have never used transactions or locked records. Once you do these, you need to apply some rules - you need to maintain the locks for the least possible time, and you need to have a strategy built in to resolve a deadlock. Does Access provide one automatically?

Clearing hibernating processes on VMS caused by locked records was always interesting. Fortunately, the access database manager has an elegant way of detecting infrequent failures with inconsistent updates.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:41
Joined
Feb 19, 2002
Messages
42,981
No. I always use optimistic locking. I've never thoroughly tested the locking options of Access. Pessimistic locking may work like repeatable read in other RDBMS' and prevent the record from being viewed if someone is currently editing it. But that's just a guess.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:41
Joined
Feb 28, 2001
Messages
27,001
Dave, I used pessimistic locking only once (in something I inherited from a less experienced Access person) and then quickly decided to find another way because it was a P.I.T.A. for sure.

My issues on VMS involved ORACLE deadlocks, and there, at least, the user would get a "deadlock" message because ORACLE had some pretty good algorithms for detecting that.

Using a SHAREBASE back-end and VMS front-end, I was able to trace back network connections to see that process X and process Y were hibernating for database wait states. I warned the developers enough when jobs would hang each other up that I was able to help them formulate rules about the order of explicit locking. If every lock is given a "preferred order" in a document that we can publish, and if every locking case ALWAYS uses the same order of locking, a "deadly embrace" type of deadlock can't occur. (Other resource waits CAN occur, sadly.) But I digress.

@hfsitumo2001 - Just so you will understand more clearly - if you are sharing data in a common back-end file, ALWAYS (when using Access) choose to minimize the time during which the data would potentially be locked. This means choosing either Optimistic locks or No locks. There are options for these on forms and queries. I believe that Domain Aggregate functions are always "No locks" but can't swear to that. However, they are usually quite safe and won't interfere with anything. SELECT queries that aren't being used incidentally for updates can also be set for No locks. Queries that do any kind of data modifications should be set for Optimistic locking. (NOTE: My OPINION and preference is "Optimistic Locking" in that case.)

EDIT: I see that Pat has also commented in favor of Optimistic locking.
 

Isaac

Lifelong Learner
Local time
Today, 05:41
Joined
Mar 14, 2017
Messages
8,738
Any time I can get end users to agree with it, I make heavy use of the concepts of assigning, re-assigning, queue-based work in my databases. This way there is a clean and natural segregation of records that aligns with them being 'used' at any given time.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:41
Joined
Feb 19, 2002
Messages
42,981
I've never had an app that had trouble with conflicts maybe because I also use a queue concept for work flow.
 

hfsitumo2001

Member
Local time
Today, 05:41
Joined
Jan 17, 2021
Messages
365
The only "gotcha" in all of this is that when creating new records from two places at once, you could get into a sticky situation if you were careless with your lock specifications. Using Optimistic Locks or No Locks, you would get minimum interference between your two hypothetical users. Using Pessimistic Locks, you would be more confident that users could not overwrite each other's data - but you would risk other errors caused by write conflicts. It's kind of a trade-off.
Isn't it automatically lock if 2 people are working on the same record?, The one is locked is the later person?, but if the other people, is just to view the record, he/she will be able right?. Let's say other people just want to see the price of an item in the inventory while the other is keying the issuance of the same item of inventory. What could it happen in this situation.

Thank you
 

Minty

AWF VIP
Local time
Today, 12:41
Joined
Jul 26, 2013
Messages
10,355
Queuing is setting up the workflow so that only one person is assigned to look at a set of specific records.
So if you are processing orders, user 1 deals with clients A to E, User 2 F to K, etc. etc.

Looking at a record will not lock it. Only editing it.
 

hfsitumo2001

Member
Local time
Today, 05:41
Joined
Jan 17, 2021
Messages
365
Queuing is setting up the workflow so that only one person is assigned to look at a set of specific records.
So if you are processing orders, user 1 deals with clients A to E, User 2 F to K, etc. etc.

Looking at a record will not lock it. Only editing it.
Usually what is the message popping up for the one who is locked
 

hfsitumo2001

Member
Local time
Today, 05:41
Joined
Jan 17, 2021
Messages
365
Dave, I used pessimistic locking only once (in something I inherited from a less experienced Access person) and then quickly decided to find another way because it was a P.I.T.A. for sure.

My issues on VMS involved ORACLE deadlocks, and there, at least, the user would get a "deadlock" message because ORACLE had some pretty good algorithms for detecting that.

Using a SHAREBASE back-end and VMS front-end, I was able to trace back network connections to see that process X and process Y were hibernating for database wait states. I warned the developers enough when jobs would hang each other up that I was able to help them formulate rules about the order of explicit locking. If every lock is given a "preferred order" in a document that we can publish, and if every locking case ALWAYS uses the same order of locking, a "deadly embrace" type of deadlock can't occur. (Other resource waits CAN occur, sadly.) But I digress.

@hfsitumo2001 - Just so you will understand more clearly - if you are sharing data in a common back-end file, ALWAYS (when using Access) choose to minimize the time during which the data would potentially be locked. This means choosing either Optimistic locks or No locks. There are options for these on forms and queries. I believe that Domain Aggregate functions are always "No locks" but can't swear to that. However, they are usually quite safe and won't interfere with anything. SELECT queries that aren't being used incidentally for updates can also be set for No locks. Queries that do any kind of data modifications should be set for Optimistic locking. (NOTE: My OPINION and preference is "Optimistic Locking" in that case.)

EDIT: I see that Pat has also commented in favor of Optimistic locking.
The Doc Man, I tried to see on the data and format property, there is no optimistic locking, where can we see it in form and query
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 12:41
Joined
Sep 12, 2006
Messages
15,614
locking is in File/Options/Client Settings

It may be somewhere slightly different depending on the Access version.
 

Users who are viewing this thread

Top Bottom