Data Update Delay in Multi-User Environment (1 Viewer)

taifoor

Registered User.
Local time
Tomorrow, 00:56
Joined
Jul 16, 2015
Messages
62
Dear All,

I have a project with one BE and multiple FE users. Each user has its own FE connected to one BE.

My problem is that i have used combo boxes in forms that limits data entry to specific tables fields (Using limit to list property). I got conflict in data entry. To understand conflict go thru following steps that lead to conflict in data entry:

1- User 1 opens a form containing a limit to list combobox based on a table in BE
2- User 1 selects data from combobox.
3- User 2 changes data in BE table that leads to remove the specific data in combobox list that user1 already selected for combobox
4- User1 saves the changes and it saves. (it must give error here that combox data do not valid now.

To solve this issue, i came to know that index property of combobox solves this problem and it solved my problem. The only thing i now face is very small lag in data update. When user 2 updates the table, User 1 FE get this data in about 3 sec and before these 3 secs, list index property do not recongnise invalid entry and saves that combobox with invalid value.

So, my question is that how can i resolve that small data delay in updating linked tables to other users.

Thanks.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 16:56
Joined
Apr 27, 2015
Messages
6,286
Good morning taifoor,

Just an observation, but if the scenario you depicted is a common occurrence, I would suggest a procedure change.

Rather than allow you users to delete records, I allow them to "flag" records for deletion. They do not go away and I filter them out of the recordsource query that populated the combobox. I then deal with those selections that are deleted/obsolete at a later date.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 20:56
Joined
Jul 9, 2003
Messages
16,245
I'm not sure I grasp the essence of your question. So I thought I would explain what I think you are doing and then you can correct me. You have combo boxes which basically provide each user with the same lookup list from which they can select. However you allow users to remove items from this lookup list.

I think that's the essence of the problem. If an item is removed from the lookup list you are effectively creating Orphaned records. In other words were ever that entry exists throughout your database now references "nothing" ...

The solution is to add an extra field to your lookup table. Add a boolean field, call it "blnDeleted".

Change the query which drives the combobox so that it only shows items where the boolean field "blnDeleted" is false. Now when a user needs to remove an item from the combo list, have a pop-up form which displays the list, along with the boolean field. The user can then check the checkbox which will convert the record into a deleted record, without removing it from your system.

One problem with this method is that if a change is made by another user then that is not instantly reflected in the combobox. One solution is to use the on enter event (I think) of the combobox to refresh it's row source.
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 16:56
Joined
Jan 23, 2006
Messages
15,364
I also do not appreciate the essence of the issue. If you have different people/users and you want to prevent manipulation of the same record simultaneously, you might consider breaking the workload up by users such that each user has her/his own set of records to work on. Again, I may have missed the gist of your post.

I recall having 5 people work on a NAICS (industry classifications) conversion to 45,000 companies. We allotted sections of the workload to specific users --primarily to prevent collisions.
 

taifoor

Registered User.
Local time
Tomorrow, 00:56
Joined
Jul 16, 2015
Messages
62
Dear All,

The problem I explained is very very common. Almost every Ms Access user will face it in multi-user projects.

I explain it again. Its very simple.

User 1 selects a field in a combobox that is valid when it is selected. e.g. combobox shows customers of USA only. i.e. country field criteria is set to "USA" of combobox rowsource property. E.g. User 1 selected John

User 2 opens customers list and changes the John customer (which user1 already selected) country from USA to say UK.

Now user1 have John in combobox which is not not valid.

But i mentioned that in order to capture that invalidity, combobox ListIndex propery is used on before update event to check combobox value again on just saving.

BUT the problem is when User2 changes customers country from USA to UK, I noticed that User1 get these changes after some small time (seems 3 sec) and within that 3 sec, user1 combobox show John as USA customer but actually it is changed to UK by User 2.

So I mean After 3 sec, User1 can get the updated data and now before update event of form gives error while checking ListIndex property of combobox.

Hence, I want to cope for the delay in updating data that is used in combobox rowsource property.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 20:56
Joined
Jul 9, 2003
Messages
16,245
Dear All,

The problem I explained is very very common. Almost every Ms Access user will face it in multi-user projects.

I explain it again. Its very simple.

User 1 selects a field in a combobox that is valid when it is selected. e.g. combobox shows customers of USA only. i.e. country field criteria is set to "USA" of combobox rowsource property. E.g. User 1 selected John

User 2 opens customers list and changes the John customer (which user1 already selected) country from USA to say UK.

Now user1 have John in combobox which is not not valid.

But i mentioned that in order to capture that invalidity, combobox ListIndex propery is used on before update event to check combobox value again on just saving.

BUT the problem is when User2 changes customers country from USA to UK, I noticed that User1 get these changes after some small time (seems 3 sec) and within that 3 sec, user1 combobox show John as USA customer but actually it is changed to UK by User 2.

So I mean After 3 sec, User1 can get the updated data and now before update event of form gives error while checking ListIndex property of combobox.

Hence, I want to cope for the delay in updating data that is used in combobox rowsource property.

Well I'm not getting anything more from your latest explanation. I don't recognise the problem, this may well be that I'm not understanding it. It might be that you are doing something that is a bit out of the ordinary. You mentioned that it's a common problem. If you could point me to some posts where other people explain the issue. Reading their explanations might give me a better understanding of it....
 
Last edited:

MarkK

bit cruncher
Local time
Today, 13:56
Joined
Mar 17, 2004
Messages
8,178
Here's where the scenario does not make sense to me...

Scenario 1:
  1. User 1 selects "John" from a combo, he's from the UK, this is fine, saves the record. This is deemed a successful update.
  2. User 2 changes "John" to come from the US.

Scenario 2: (the problem case)
  1. User 1 selects "John" from a combo, he's from the UK, this if fine...
  2. User 2 changes John's country of origin to come from the US.
  3. User 1 saves the record.

In both scenarios the data is incorrectly stored. The fact that in scenario 2 there might be a collision is irrelevant. They both result in john being erroneous selected.

I think the collision is a red herring, or am I missing something?
Mark
 

CJ_London

Super Moderator
Staff member
Local time
Today, 20:56
Joined
Feb 19, 2013
Messages
16,553
I was about to respond, then saw Marks post so built on that (basically a rephrasing).

I think if you change Mark's scenario slightly, it explains the OP's problem
I noticed that User1 get these changes after some small time (seems 3 sec) and within that 3 sec, user1 combobox show John as USA customer but actually it is changed to UK by User 2.

Scenario 2: (the problem case)
  1. User 1 selects "John" from a combo, he's from the UK, this if fine...
  2. User 2 changes John's country of origin to come from the US.
  3. If User 1 saves the record within 3 seconds of the User 2 update he does not get a warning in the form before update event that John is no longer in the UK - and presumably the save is cancelled.
The question being asked is about the 3 seconds. How to reduce it?

But I do agree with Mark - all other records which have previously selected John on the basis he is in the UK will now be wrong. So there should be some code around changing John's location that warns user 2 that other records will be affected - or you need some sort of audit query that will flag up the records now containing the 'wrong' data, or the user record is locked down, or something else. All depends on what you expect to happen in that eventuality.

Either way, yes it is a 'common' issue with multiuser databases, but it should be identified as a process issue during the design phase and handled through database design. And fixing the 3 second problem (if it can be) does not solve the underlying problem with the database.
 

taifoor

Registered User.
Local time
Tomorrow, 00:56
Joined
Jul 16, 2015
Messages
62
I was about to respond, then saw Marks post so built on that (basically a rephrasing).

I think if you change Mark's scenario slightly, it explains the OP's problem


Scenario 2: (the problem case)
  1. User 1 selects "John" from a combo, he's from the UK, this if fine...
  2. User 2 changes John's country of origin to come from the US.
  3. If User 1 saves the record within 3 seconds of the User 2 update he does not get a warning in the form before update event that John is no longer in the UK - and presumably the save is cancelled.
The question being asked is about the 3 seconds. How to reduce it?

But I do agree with Mark - all other records which have previously selected John on the basis he is in the UK will now be wrong. So there should be some code around changing John's location that warns user 2 that other records will be affected - or you need some sort of audit query that will flag up the records now containing the 'wrong' data, or the user record is locked down, or something else. All depends on what you expect to happen in that eventuality.

Either way, yes it is a 'common' issue with multiuser databases, but it should be identified as a process issue during the design phase and handled through database design. And fixing the 3 second problem (if it can be) does not solve the underlying problem with the database.

Dear CJ_London,

Based on ur answer, I think that you have understand my problem well. You mentioned:

"But I do agree with Mark - all other records which have previously selected John on the basis he is in the UK will now be wrong. So there should be some code around changing John's location that warns user 2 that other records will be affected - or you need some sort of audit query that will flag up the records now containing the 'wrong' data, or the user record is locked down, or something else. All depends on what you expect to happen in that eventuality."

I have the code that will not allow to change US customers to be changed to other countries if its in use in any other table. So this inaccuracy is solved.

But the problem remains i.e. due to delay in of about 3 secs data sync to User 1 from User 2.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 20:56
Joined
Feb 19, 2013
Messages
16,553
So the situation you are describing is
  1. User 2 creates a 'John' record with origin of UK
  2. for a new or existing record User 1 is the first user to select "John (from the UK)" with a combo
  3. Whilst User 1 is creating their record, User 2 realises their error and changes John's country of origin to come from the US - at that point there are no records to trigger a warning for User 2
  4. If User 1 saves the record within 3 seconds of the User 2 update he does not get a warning in the form before update event that John is no longer in the UK - and presumably the save is cancelled.
It has never been an issue for me so I can't recommend any specific solution, just offer some suggestions - which may result in other unwanted side effects.

It may have something to do with your network performance (from both user1 and user2 perspective) , type of backend and/or table design - I presume the relevant tables are properly indexed. And if the tables are encrypted or use calculated or multivalue fields this may introduce a delay.

Not sure if it would help but to investigate I would adding a datetime field to your table, populated in user2's form before update event.

Then modify your combo rowsource to include now() as a second timestamp. Comparing the two might give you a clue.

Other things to investigate include looking at the db client settings (needs to be done for each user) - File>Options>Client Settings Advanced section refresh interval.

If you are using 2010 or later and data macros then perhaps you need to set the form 'wait for post processing' property to true.


Good luck with your project
 

taifoor

Registered User.
Local time
Tomorrow, 00:56
Joined
Jul 16, 2015
Messages
62
So the situation you are describing is
  1. User 2 creates a 'John' record with origin of UK
  2. for a new or existing record User 1 is the first user to select "John (from the UK)" with a combo
  3. Whilst User 1 is creating their record, User 2 realises their error and changes John's country of origin to come from the US - at that point there are no records to trigger a warning for User 2
  4. If User 1 saves the record within 3 seconds of the User 2 update he does not get a warning in the form before update event that John is no longer in the UK - and presumably the save is cancelled.
It has never been an issue for me so I can't recommend any specific solution, just offer some suggestions - which may result in other unwanted side effects.

It may have something to do with your network performance (from both user1 and user2 perspective) , type of backend and/or table design - I presume the relevant tables are properly indexed. And if the tables are encrypted or use calculated or multivalue fields this may introduce a delay.

Not sure if it would help but to investigate I would adding a datetime field to your table, populated in user2's form before update event.

Then modify your combo rowsource to include now() as a second timestamp. Comparing the two might give you a clue.

Other things to investigate include looking at the db client settings (needs to be done for each user) - File>Options>Client Settings Advanced section refresh interval.

If you are using 2010 or later and data macros then perhaps you need to set the form 'wait for post processing' property to true.


Good luck with your project

Dear CJ_London,

Thnaks for your suggesstions. Yes I have Backend encrypted but i have check by decrypting but the problem still there.

Also applied your other suggesstions too but same results. Moreover, I am testing on same PC while opening FE multiple times. So network delay is not possible.

It seems that its natural delay and i think it can be coped with some unique trick/tip. Again Thanks for ur time and suggesstions.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 20:56
Joined
Feb 19, 2013
Messages
16,553
if the scenario I have painted is correct, the likelihood of it occurring is very small. But it can occur.

So I would have some data integrity/validation queries to run on occasion.

I use what I call a 'greenboard'. The sort of thing you might see on a submarine - all lights need to be green before you can dive.

This is a simple count query which is then applied to conditionally format a text box.

Users clicking on the box can then display a list of the affected records, select one and are taken to the relevant form to correct, or the list used to filter a navigation form.

Actual workings is up to you - queries may be refreshed when the main form has received the focus, or another navigation form opened. Certain processes are disabled until issues are resolved etc.
 

Users who are viewing this thread

Top Bottom