Slow reports access2016 with backend Mysql dB (1 Viewer)

Harry_38

Registered User.
Local time
Today, 01:26
Joined
Jan 14, 2011
Messages
47
Administration program for a non profit organisation, that organizes courses and has 800 members. Front-end Access2016 with backend mysql dB; works fine. We work with 5 volunteers in different locations; they have access to read and write.
You start the admin program by selecting an administration year. The selected year is saved in the field 'Year' in a backend table 'General'. Program responds with forms and reports that belong to the selected year.
All goes well if all volunteers work in the same year. Problem arises when person A selects year 2021 and person B follows later and selects 2020. B overwrites the field 'Year' in table 'General' and person A notices that the year has changed form 2021 into 2020. Hence it is impossible to work in 2 different years by 2 persons at the same time.
I thought to have found a solution to the problem by moving tbl 'General' from mysql backend to Access2016 frontend. It did solve that problem but created a new problem i.e. ; reports involving 5 different tables give a very slow response, up to 30 secs.
Please help with suggestions on how to solve this response problem with the reports.
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:26
Joined
Sep 21, 2011
Messages
14,048
So that table should have a field to identify user?, or hold that data locally?
 

Harry_38

Registered User.
Local time
Today, 01:26
Joined
Jan 14, 2011
Messages
47
Thanks for the reply Gasman.
The user is nowhere identified, neither in de frontend nor in the backend.
I think I see what you mean. The startup form should ask for a username. And I would have to add a new field 'UserName' in the General table and link the selected year with the user.
Correct?
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:26
Joined
Sep 21, 2011
Messages
14,048
Yes, if you want years for individual users?, else store that data in the FE where it will only be available to that user?
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:26
Joined
Feb 19, 2002
Messages
42,981
There is nothing wrong with the suggestion to modify the "year" table to include the UserID so that each user can have his own "year" selection.

Another way to do this is to have the user log in and select the year. When he clicks OK to go to the menu, hide the login form rather than closing it. Then your queries can use criteria that references the hidden login form rather than the table.

You're going to have to change all your queries to implement either suggestion otherwise I wouldn't have mentioned the alternative.
 

Harry_38

Registered User.
Local time
Today, 01:26
Joined
Jan 14, 2011
Messages
47
I'll study the suggestions of Gasman and Hartman; it will take me some time.
Thanks very much so far
 

Users who are viewing this thread

Top Bottom