Another slow Microsoft Access file on network properly split on front-end and back-end - How to solve?

Need some more details:

1. What version?
2. What OS is the BE on?
3. By multi-user, do you mean only one user in the DB, then #2 comes along and it takes forever to start the app, or are they both in and queries just slow down.
4. Is this a new setup?
5. Did this just start or has it been on-going?

Jim.

Review http://www.devhut.net/2017/04/09/setting-up-an-ms-access-database/

Have you tried housing the db on another PC/Server share?  Any difference?
Do you establish a persistent connection at the startup of your database?
Have you compacted the database (both BE and FE)?

Does everyone have their own personal copy of the FE (they should)?  Some people make the mistake of allowing all users to open the same physical copy of the FE (very bad practice).

Jim

1. multiple versions.  Most have Access 2016 (Office 365)
2. OS - on a shared folder (not sure what type of server)
3. only one user in their own copy of their fe... #2 comes along on their local copy of their fe and it takes forever to start the app and access the data entry screen even if tied to temp local fe copies only.
4. new setup - yes
5. originally was not split into fe/be.

Daniel,
1. not yet.
2. yes but seemed to slow it down further and lock others
3. yes - both.   I always do before putting the fe on the network.  Just did the back-end again and will see if that helps

PatHarman,
1. yes... their own copy of the FE.  This is what slowing down itself.  and the funny thing nothing in the startup is tied to the backend....   It just so happens its connected to the back end.

Just a thought - is everyone using the same version of Access?  Using different versions of Access to run the fe connected to a shared be may be a factor.

Also, multiple people using the same fe file at the same time is not only a real performance "drag', bug, but not all of the things a developer might put into the design of an fe will work in a "shared file" situation.  

There are several things like using code to change the .SQL property of a querydef object, or putting a form/report in design mode to change it with VBA code, or clicking the "Save" button if prompted to save form/report changes - even if you think you didn't make any.

In these situations, whoever makes the last change WINS! (everybody else gets the same change immediately!  Talk about things changing before your very eyes......)

again, the fe is a local c:\drive separate .accdb linked to the backend file on a network \\ folder file.

Yes, I read it earlier - just commenting on WHY using a single fe for multiple users is a "bad idea" since everyone else said "it's a bad idea".  

"multiple versions.  Most have Access 2016 (Office 365)"
Does the slowdown occur when a user with a different version of Access tries to run the fe?  I haven't seen the answer to that yet....

I do know that whoever opens the db first usually locks out all others with a different version of Access, or at least it causes major performance issues.  That I know because I've seen it....in person... in a corporate environment.

Perhaps a bit more detail is needed:
In a case where more than one person is attempting to use the same fe file (not your situation), the first person/Access version to open the file completely locks out all other versions of Access from using the file.

Since the personal version of Access is also processing the data on the back-end as well as running the front-end, it can severely hamper/exclude any other version from using the be as long as it is processing any data in the tables on the be database.

See if that is your problem.

Since most development environments don't test this issue, they never know about it.

I appreciate your comments Mark.
Sorry I misunderstood what you were trying to tell me.

"Yes, I read it earlier - just commenting on WHY using a single fe for multiple users is a "bad idea" since everyone else said "it's a bad idea".  
OK.

"Does the slowdown occur when a user with a different version of Access tries to run the fe?  I haven't seen the answer to that yet...."
No the guy who locks everyone else out has Access 2016 Office 365 - same version as mine.

"Since the personal version of Access is also processing the data on the back-end as well as running the front-end, it can severely hamper/exclude any other version from using the be as long as it is processing any data in the tables on the be database."

The guy opens an old legacy data entry form (with the prev, next records-add new bar) that originally was tied directly to the table.   I put in a query and the recordsource is set to that currently.  I'm thinking about making a temp table and know certainly that will speed things up but then I'll have to send the changes to the real table - edit, add, delete and then what table will be next after that?    Thus was another lead to a another question here:
https://www.experts-exchange.com/questions/29133987/Need-Microsoft-Access-plugin-or-template-that-takes-all-tables-converts-to-temp-and-then-reassigns-all-forms-queries-to-that-temp-along-with-doing-data-validation-and-reconciliation-between-them.html?headerLink=workspace_open_questions

He's running Windows 10 as well as myself and another.   Few have Windows 7 and Access 2010 like 3 others.
But whoever opens that form - locks everyone out of the database.  What are some steps I can do to make this form not lock out others besides a temp table submitting updates?

It seems like because this other guy has that data entry form open.... that when another opens their local C:\drive copy - that MS Access database first thing tries to connect to the tables it's linked and pauses everything.    Like we are talking about removing the startup form and it still does it.   And the last time I seen this long ago was because the server needed a certain setting that kept this from happening.   But it was like 6 plus years ago.

I mean it hangs the whole application - like a big solid black line around the whole application keeping anything from running.

What is/are the lock setting(s) for these tables?

How may I tell what the lock settings are for these tables, aikimark?
I don't know where to go in Office 365 - Access 2016

There used to be this way:

http://www.databasedev.co.uk/multi-user-application-record-locking.html

The only other time I saw it this slow was when I was having conflict with HP fingerprint software:  

https://answers.microsoft.com/en-us/msoffice/forum/all/access-2010-freezes-and-shuts-down/0d24a3a0-aa7d-e011-9b4b-68b599b31bf5

Still waiting on IT to fix the server settings.

I did implement these suggestions at the following but it still hangs
http://www.granite.ab.ca/access/performancefaq.htm

Every line item I have it the following - it hangs and doesn't continue after several seconds.
Thinking about changing it to ADO.

Set rs = CurrentDb.OpenRecordset(sSQL)

Select allOpen in new window

Just be aware that a call to CurrentDB() fetches a new database object each time, with refreshed collections.  If this is a big app, that might take some time.   Checkout:

https://www.experts-exchange.com/articles/2072/CurrentDB-vs-dbEngine-Workspaces-0-Databases-0-and-an-alternative.html

 with code which you can use as a replacement, or just put things in a database variable.  i.e.

 Dim db as DAO.Database

Set db = CurrentDB()

Set rs = db.OpenRecordset(sSQL)


 which is basically what the article code does, but on an app wide scale.

Jim.

This is starting(?) to sound like a real mystery.  In cases like this, I disable/disconnect everything and proceed under the conditions that are causing the app to lock up and try to isolate what the problem is by bringing the other parts of the system back one-by-one until the problem occurs.  Using this method, I would ask these questions:

1.  With no tables linked or startup form/code running (just open the file and nothing but the file - period), does the problem occur?  (Is just trying to open a dead Access file experiencing the issue?  Local hardware/software issue?)
2.  Link tables from a different be (that no one else is using but in the same location as the original be) and try again.  Does the problem occur?  (Network or hardware issues...  Access be file issue?)  Run a query on startup, but nothing else.  Does the problem occur?  (Jet or data processing issue?)
3.  Link one or more tables from the original be, but do nothing else, and try again.  Does the problem occur?  (Problem with be file and/or tables).
4.  Run a query on startup, but nothing else.  Does the problem occur?  (Jet or data processing issue?)

Keep going along this line of investigation until the issue occurs.  What added process caused the issue to all of a sudden start to occur? This should help you identify what technology is involved.  In other issues I've seen like this, its usually where the person overlooks something because they don't think it could possibly be the problem.  Imagine their surprise (and red-face) when they finally discover it is!
It's worth a shot.... Better than beating your head against the wall trying to guess exactly what it is.......

So far the server SMB settings have been applied and it's moving a lot quicker now.  

Personally, Jim I've tried to incorporate your advice but whenever an error occurs and moving through multiple subs and functions the database variable breaks and fails to hold on to the value throughout the process.   What I did instead was try to incorporate more custom Microsoft Access custom properties and looking into Dictionary also to store more there than accessing CurrentDB.

<<Personally, Jim I've tried to incorporate your advice but whenever an error occurs and moving through multiple subs and functions the database variable breaks and fails to hold on to the value throughout the process.  >>

 The variable should be declared globally and if you reset, the next call will fetch the value the way it's written (the object variable will be nothing).

Jim.

Even declared globally - it fails when there is an error in any sub or function using that global variable inside the module or outside in a global module.