UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Multi-user - Contention Issues?, Access 2016    
 
   
daveinaz
post Apr 3 2020, 05:44 PM
Post#1



Posts: 13
Joined: 7-September 18



Hi,

I've built a small but complex Access 2016 database that works perfectly for a single user. When more than one person attempts to use it at the same time, we run into what I assume are contention issues, although I've tried to prevent those. If I enter a new record at the same time as another user, one of us is likely to get only a partial insertion and may end up looking at the other's record unintentionally. There's no problem with seeing each other's records, it's just that the application is navigating without the user's input. That's the gist of the problem.

This is a split database, with a shared back end and each user having the front end on their own machines, working across an internal network. Default Open Mode is Shared. Default Record Locking is Edited Record, throughout.

The core functionality of the db is contained in one form, but that form has a sub-form, which also has a sub-form. So, a one-to-many-to-many design. The design is due to the nature of the application and the users. It is to be used by our QA department, and there are a good number of data points involved. So, each review (main form) record contains metadata such as case #, review type, reviewer, and so on. There are 3 types of review, each with its own unique set of questions, though some questions appear more than once per set and in multiple sets. The first sub-form (questions) really only contains one question per record and the sub-subform (sub-questions). The sub-question form is displayed in scrollable continuous mode in order to have enough screen real estate to accommodate all the sub-questions. There can be anywhere from 0 to 39 (currently, though it's unlimited) sub-questions per question.

This is what it looks like.

Attached File  49732489891_bcbf7c1414_b.jpg ( 135.8K )Number of downloads: 6


At the moment, I'm getting "Could not update; currently locked by another session on the machine. (Error 3188)" when I try to add a new record. I can only assume that that's because I added Cascade Updates to the relationship in the back end, trying to resolve the complaints from Access about referential integrity. Obviously, that didn't work, and that error didn't occur before I did that.
But, normally, adding a new record takes these steps;
a new blank screen is displayed
a routine in the Reviewer field's AfterUpdate event opens a Transaction, runs vba queries that insert the Review record, delete any leftover Answer records for the Case and Set type chosen for the new review, then inserts a set of Answer records into the answer table so that
they can be displayed on the form, closes the Transaction, and Refreshes the form.

It's the Answer records that cause all the problems. Either the Answer records don't get created, at all, or they get created but not displayed. Again, these problems ONLY occur when multiple users are working in the db.

Any helpful suggestions? Thanks!
This post has been edited by ghubbell: Apr 4 2020, 10:34 PM
Reason for edit: NO OFF SITE LINKS OR PICS PLEASE
Go to the top of the page
 
theDBguy
post Apr 3 2020, 06:11 PM
Post#2


UA Moderator
Posts: 78,098
Joined: 19-June 07
From: SunnySandyEggo


Hi Dave. Welcome to UtterAccess! welcome2UA.gif

On the screenshot you just posted, the first record in the continuous subform is locked (by another user?), which means you shouldn't be able to modify it. Is this by design?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
daveinaz
post Apr 3 2020, 07:17 PM
Post#3



Posts: 13
Joined: 7-September 18



Hi DBguy. Thanks!

No, that sub-subform should not be locked. I went back into the back end and turned off the cascading update/delete options, and that seems to have fixed that problem. And possibly the 3188s, but I haven't checked that, yet.
Go to the top of the page
 
daveinaz
post Apr 3 2020, 07:21 PM
Post#4



Posts: 13
Joined: 7-September 18



Well, now I've checked and it did NOT cure the 3188 errors.
Go to the top of the page
 
daveinaz
post Apr 3 2020, 10:27 PM
Post#5



Posts: 13
Joined: 7-September 18



Hi DBguy. Thanks!
QUOTE
On the screenshot you just posted, the first record in the continuous subform is locked (by another user?), which means you shouldn't be able to modify it. Is this by design?

No, that should not be locked. I went back into the back end and removed the cascading update/delete options and now the lock is gone. Causal or coincidental? I don't know.
Go to the top of the page
 
tina t
post Apr 4 2020, 12:38 AM
Post#6



Posts: 6,591
Joined: 11-November 10
From: SoCal, USA


can you post a screen print of your Tables/Relationships window?

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
arnelgp
post Apr 4 2020, 05:34 AM
Post#7



Posts: 1,528
Joined: 2-April 09
From: somewhere out there...


the problem with your coding is using SQL
statement to add/update record, which will literally
lockdown the table while it is being executed.

imagine two users executing an insert query at
same time.

same is true with Update query.

you should have a Bound form to add or update
records. thereby limiting only the lock on that
record.

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
nvogel
post Apr 4 2020, 07:21 AM
Post#8



Posts: 1,103
Joined: 26-January 14
From: London, UK


Have you considered using SQL Server or another SQL DBMS? That would give you better scalability and multi-user support than you can get from an ACE database. If your database contains personally identifiable information then it really should be in a SQL DBMS for reasons of security and integrity.
Go to the top of the page
 
daveinaz
post Apr 4 2020, 06:25 PM
Post#9



Posts: 13
Joined: 7-September 18



QUOTE
can you post a screen print of your Tables/Relationships window?

Yes. Here you go
Attached File  49736420806_fbaf1f7452_b.jpg ( 39.7K )Number of downloads: 8

This post has been edited by ghubbell: Apr 4 2020, 10:35 PM
Reason for edit: NO OFF SITE LINKS OR PICS PLEASE
Go to the top of the page
 
daveinaz
post Apr 4 2020, 06:29 PM
Post#10



Posts: 13
Joined: 7-September 18



QUOTE
the problem with your coding is using SQL
statement to add/update record, which will literally
lockdown the table while it is being executed.

imagine two users executing an insert query at
same time.

same is true with Update query.

you should have a Bound form to add or update
records. thereby limiting only the lock on that
record.

May very well be true, but have you got an example? It's been 20 years since I did anything serious in Access, and this was the best I could come up with.

I hoped that using transactions would prevent clashes, but it doesn't seem to. And I can't even picture what a single bound form to handle this situation would look like.
Go to the top of the page
 
daveinaz
post Apr 4 2020, 06:34 PM
Post#11



Posts: 13
Joined: 7-September 18



QUOTE
Have you considered using SQL Server or another SQL DBMS? That would give you better scalability and multi-user support than you can get from an ACE database. If your database contains personally identifiable information then it really should be in a SQL DBMS for reasons of security and integrity.

No argument from me. I've done SQL programming in both Oracle and SQL Server, but I'm afraid it's not allowed in my current situation. I am pulling a lot of lookup data from the SQL Server, but I can't build tables or logic on the server.
Go to the top of the page
 
tina t
post Apr 4 2020, 09:45 PM
Post#12



Posts: 6,591
Joined: 11-November 10
From: SoCal, USA


well, it's hard to read the screenshot you posted, and i'm not going to an external website to see it bigger. what i can make out is that you haven't enforced referential integrity in most of your relationships. i don't know if that's contributing to your issue, but i can tell you that drawing lines between tables without enforcing referential integrity is basically a waste of time.

want to try uploading an actual attachment in a post, so we can look at it here?

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
daveinaz
post Apr 6 2020, 11:33 AM
Post#13



Posts: 13
Joined: 7-September 18



I'm not sure it will look any better, but I've attached the relationship screenshot to this post (I think. It doesn't show up in the preview). I can just double-click on the embedded one above and it opens in a new window at full life-size. Can other people not do that?

It's true that most of these relationships are not enforced, because Access won't allow it. I assume that's because not all the primary key fields in the Sets table exist in the other tables. It doesn't seem likely to be the cause of my problems. The Sets and Questions tables are never updated during normal use. The Answers-Sets-Questions group is only used to populate the Answers table with the appropriate set of questions to be answered based on the user's selection. When a user creates a new Review, a Set of Answer records (between 108 and 125 of them) are inserted into the Answer table, and those are joined to the related Questions for display.

I hope I'm explaining this well enough.
Attached File(s)
Attached File  ClientRel.JPG ( 44.53K )Number of downloads: 2
 
Go to the top of the page
 
tina t
post Apr 6 2020, 02:09 PM
Post#14



Posts: 6,591
Joined: 11-November 10
From: SoCal, USA


recommend you fix your tables/relationships before going further, with the goal of enforcing Referential Integrity on all relationships. suggest you use a single-field surrogate primary key for each table, Autonumber is easiest. this will eliminate the need for cascade updates from parent to child records, while ensuring no orphan records in child tables. if you want to ensure unique records re data, use multi-field indexes as needed in tables.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
daveinaz
post Apr 6 2020, 04:18 PM
Post#15



Posts: 13
Joined: 7-September 18



Hi Tina.

I appreciate the effort you've put in. I guess I didn't explain it clearly enough. The Sets and Questions tables don't even need to be in the Relationship diagram or have referential integrity because they're not involved in capturing data. They're only used to create the answer records or to display the questions, but not to store any input from the user. They're not ever being written to by the application, so they can't be involved in the locking issues.

I also can't use an autonumber field as the index on the Sets table, due to its nature. It's effectively the join table in a many-to-many relationship. I would need to add autonumbers to each of the other tables and manually add corresponding values to two new fields in the Sets table. And I don't see what that would buy me because two of the tables are not intended to be updatable.

Maybe it would help to see what's in the Sets table, so here's a small portion of it.
Attached File  tblSets.JPG ( 37.85K )Number of downloads: 1


While I'm at it, here is a snippet of tblAnswers
Attached File  tblAnswers.JPG ( 41.28K )Number of downloads: 0


And tblQuestions
Attached File  tblQuestions.JPG ( 57.61K )Number of downloads: 1


And we might as well include tblReviews
Attached File  tblReviews.JPG ( 33.49K )Number of downloads: 3

Go to the top of the page
 
tina t
post Apr 6 2020, 10:57 PM
Post#16



Posts: 6,591
Joined: 11-November 10
From: SoCal, USA


well, you know best, i'm sure. good luck with your project. tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
daveinaz
post Apr 8 2020, 10:51 AM
Post#17



Posts: 13
Joined: 7-September 18



QUOTE
well, you know best, i'm sure. good luck with your project. tina

Well, bless your heart. Aren't you the sweetest thing?

Someone correct me if I'm wrong, but I remember Referential Integrity's purpose as being to prevent two tables from getting out of sync with each other as the result of insertions or updates. That's why I have it enforced on the relationship between Reviews and Answers. It would take a lot of work to redesign the Sets and Questions tables to accommodate Referential Integrity, and I'm not doing insertions or updates on the Sets or Questions tables, so what would be the purpose of enforcing Referential Integrity on them? Is there one?

Any other suggestions?
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    27th May 2020 - 07:20 AM