Slow Loading Form on FE (1 Viewer)

psyc0tic1

Access Moron
Local time
Today, 15:30
Joined
Jul 10, 2017
Messages
360
Hi all,

I have a split database with the back end stored on a server in another state on our network. It has to be where it is and that is all I can say about that.

The front end has several forms for data input. All open fairly quick but I have one form that has 118 fields on it plus a combo box (so I guess it is 119). This form takes anywhere from 90 seconds to 180 seconds (sometimes longer depending on network) to open and the only reason I can deduce is due to having so many fields on it.

I have tried to hide a lot of the fields that do not always need input and then call them back into visibility with checkboxes but this of course does not solve the problem.

Does anyone have any code ideas to help with this solution?

This is the same form I posted about a while back asking about hiding fields and making them visible on criteria but the database was then not a split one and this was not an issue.

In advance I have read the many sites with all of the tricks for speeding up performance up to and including the persistent connection which I have used but did not help.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:30
Joined
Aug 30, 2003
Messages
36,118
It may be how many records are being pulled in for that form, rather than how many fields (the bottom line is how much has to be pulled over the network). How many records are in the form's record source? Can that be reduced? The combo is on top of that, how many records in its row source? Can that be pulled later, once the user has made some other selection to reduce the number? Or can the combo source be local, perhaps loaded when the app opens?
 

Mark_

Longboard on the internet
Local time
Today, 13:30
Joined
Sep 12, 2017
Messages
2,111
Another conceptual trick, not to sure how easily it would be in your case though;

One query that returns just the information needed to identify which record you want to deal with.

When you do want to update the record, then and only then request that specific record.

This would mean you are requesting N records with three or four fields rather than N records with 119. That said, what back end are you dealing with? If it is a true back end server this can dramatically improve performance. Haven't tried the same with just a split database though.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 16:30
Joined
Apr 27, 2015
Messages
6,286
Give this a read:
http://www.kallal.ca/wan/wans.html

He originally wrote it in 2003, but has updated up to and including AC2010. Because I had two remote sites in Europe and the Middle East, a very narrow minded IT department, and SharePoint readily accessible, that is the route I took.

Sharepoint is NOT the most reliable and smoothest of operations...but...it has solved my speed and most importantly, corruption issues.

I also had to incorporate some tips I gleaned from the FMS inc website, one in particular is converting seldom updated tables to local tables. It seemed almost like blasphemy to my split DB mindset, but shared tables on a LAN (as you have discovered) has its limitations.
 

missinglinq

AWF VIP
Local time
Today, 16:30
Joined
Jun 20, 2003
Messages
6,423
When I saw your post I was going to suggest Albert Kallal's site, as well...it's really been the gold standard for solving this kind of problem.

Linq ;0)>
 

psyc0tic1

Access Moron
Local time
Today, 15:30
Joined
Jul 10, 2017
Messages
360
My database is designed for recording test data for electrical parts. There are 3 steps for recording this data.
Step 1) On the first form I start a record by inputting a facility that received a shipment of parts. The facility number, purchase order number, part number, quantity received, and the date it was received. Submit record. This sets a "status" of "Waiting on Visual Inspection".
Step 2) On the second form there is a combo box that displays the PO number, the status, and the part number joining the part number description (pulled from a lookup table). On this form, choosing a selection from the combo box auto populates the part number field, the po number field, the quantity received field, the facility number field, and the part number field (so the person inputting the data on this form knows what they are dealing with) and they have to fill out the rest of the fields with their collected data. Subnit record changes. This then changes the "status" from "Waiting on Visual Inspection" to "Waiting on Lab Test".
Step 3) On the third form (this is the one that load bad slow) also has a combo box that displays the PO number, the new status, and the part number joining the part number description (pulled from a lookup table). On this form, choosing a selection from the combo box auto populates the part number field, and the po number field (so the person inputting the data on this form knows what they are dealing with) and they then have to fill out the remaining fields with their test data. The issue with this form is that there are a lot of fields on it pertaining to USB port amps testing and all of them must contain default values of zeros because not all parts to be tested have USB ports and due to stupid people they cannot be trusted to do things right. All of these zeros are pulled from the data table just like the information stated above. Submit record changes. This then changes the "status" from "Waiting on Lab Test" to "Complete" and there are no more steps to this record.
It may be how many records are being pulled in for that form, rather than how many fields (the bottom line is how much has to be pulled over the network).
How many records are in the form's record source?
There is a single data table with about 1100 records in it. There are 7 tables with lookup information.
Can that be reduced?
not quite sure how to reduce the number of records in the table as it is the stored information that we need for each part number that is associated with purchase orders.
The combo is on top of that, how many records in its row source?
There are 4 The PO number, the status, the part number and part description combined.
Can that be pulled later, once the user has made some other selection to reduce the number? Or can the combo source be local, perhaps loaded when the app opens?
I really have no idea how any of that would be accomplished.
 
Last edited:

psyc0tic1

Access Moron
Local time
Today, 15:30
Joined
Jul 10, 2017
Messages
360
Another conceptual trick, not to sure how easily it would be in your case though;

One query that returns just the information needed to identify which record you want to deal with.

When you do want to update the record, then and only then request that specific record.
I was actually thinking about that last night before I went to bed. not sure how to accomplish it yet but is a good idea.

This would mean you are requesting N records with three or four fields rather than N records with 119. That said, what back end are you dealing with? If it is a true back end server this can dramatically improve performance. Haven't tried the same with just a split database though.
Not a true back end server... just a split database with the backend of it on the network server.
 

psyc0tic1

Access Moron
Local time
Today, 15:30
Joined
Jul 10, 2017
Messages
360
Give this a read:
http://www.kallal.ca/wan/wans.html

He originally wrote it in 2003, but has updated up to and including AC2010. Because I had two remote sites in Europe and the Middle East, a very narrow minded IT department, and SharePoint readily accessible, that is the route I took.
Thank you for the link... I started reading it and I need to spend some quality time with that to fully understand some of what I saw. Since I am not an administrator on any of our network (or anything else) I am sure I will run into hassles with our IT department over many of the solutions I saw.

Sharepoint is NOT the most reliable and smoothest of operations...but...it has solved my speed and most importantly, corruption issues.
I was also attempting to use the sharepoint method with one of our database administrators but it broke a lot of things in the access database once that was implemented. We decided that wasn't the way to go.

I also had to incorporate some tips I gleaned from the FMS inc website, one in particular is converting seldom updated tables to local tables. It seemed almost like blasphemy to my split DB mindset, but shared tables on a LAN (as you have discovered) has its limitations.
I also tried that... I made all of the lookup tables local and just left the one data table on the back end but it didn't change anything.

I was excited when I opened the front end this morning and everything was operating lightening fast.... but alas it was short lived... back to slow again after 30 minutes.
 

Mark_

Longboard on the internet
Local time
Today, 13:30
Joined
Sep 12, 2017
Messages
2,111
psyc0tic1,

With your back end in another state, do you have a dedicated machine you can have running a small process over there? If so, you could generate a list of the four fields you need for lookups and copy it local each morning. The same process could then check every few seconds for a request and produce the one record you need to send to a given remote computer.

Haven't had to do this in a loooong time, and then it was in a different programming environ. Remote site was connected by a 256k connection. Remote front ends would request by submitting .txt files identifying the record ID for the record they wanted. We then placed a corresponding .txt file in a retrieval directory for the front end to read in. Worked very very well in that slow of an environment. Allowed us to support 50-60 end users with only a two to five second delay in record retrieval. This included full account information as well as current subscriptions and comments, so it make fairly large text files (5-10K).
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:30
Joined
Aug 30, 2003
Messages
36,118
Regarding:

"not quite sure how to reduce the number of records in the table as it is the stored information that we need for each part number that is associated with purchase orders."

I wasn't suggesting deleting them from the table. If when you open the form it retrieves all 1100 records, that takes time. Is there a way they can enter a date and only get the records for that date, or something like that? You want to minimize what has to travel across the wire.
 

psyc0tic1

Access Moron
Local time
Today, 15:30
Joined
Jul 10, 2017
Messages
360
Regarding:

"not quite sure how to reduce the number of records in the table as it is the stored information that we need for each part number that is associated with purchase orders."

I wasn't suggesting deleting them from the table. If when you open the form it retrieves all 1100 records, that takes time. Is there a way they can enter a date and only get the records for that date, or something like that? You want to minimize what has to travel across the wire.
I understand what you are saying now. That would be a bit tricky and here is why I think so.

While there are many records in the table that are considered "Complete", there are also a great many in there that are waiting for someone to update their part of them and sometimes that takes weeks so a date entry would then rely on the end users applying brain power to keep up with how far back they need to look. Brain power of the end users has been problematic from inception.

I actually had to create an export feature to allow me to export to Excel and email all end users weekly on Mondays showing them they have records that need updating and showing then highlighted in red ones that were 14 days old or older... having to keep pushing them to keep up with their updates.

Let me think about this for a little while. Maybe separating the records in different tables by status? I think this will be pretty difficult as all records start out in the status of "Waiting on Visual Inspection" (this status is pulled from the lookup table called "Status"). Once someone uses the "Visual Inspection Input Form" to input that data into that same record, that record is then set to the status of "Waiting on Lab Testing" (this status is pulled from the lookup table called "Status"). Once someone uses the "Lab Test Input Form" to input that data into that same record, that record is then set to the status of "Complete" (this status is pulled from the lookup table called "Status") and the entire record then only needs to be accessed for reporting.

I can't think of how to have the data jump from table to table (let's say the tables are called "tbl_waitVis, "tbl_WiatLab", "tbl_Complete") finally resting in a table called complete.
 

psyc0tic1

Access Moron
Local time
Today, 15:30
Joined
Jul 10, 2017
Messages
360
psyc0tic1,

With your back end in another state, do you have a dedicated machine you can have running a small process over there? If so, you could generate a list of the four fields you need for lookups and copy it local each morning. The same process could then check every few seconds for a request and produce the one record you need to send to a given remote computer.

Haven't had to do this in a loooong time, and then it was in a different programming environ. Remote site was connected by a 256k connection. Remote front ends would request by submitting .txt files identifying the record ID for the record they wanted. We then placed a corresponding .txt file in a retrieval directory for the front end to read in. Worked very very well in that slow of an environment. Allowed us to support 50-60 end users with only a two to five second delay in record retrieval. This included full account information as well as current subscriptions and comments, so it make fairly large text files (5-10K).
I would never be able to get our IT to create something like that for this... they are a pain in the arse to put it mildly.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:30
Joined
Aug 30, 2003
Messages
36,118
I don't like the thought of moving things from table to table. A status field you could filter on would be better in my view. Can they enter a project number (or the appropriate key field) instead of a date?
 

Mark_

Longboard on the internet
Local time
Today, 13:30
Joined
Sep 12, 2017
Messages
2,111
psyc0tic1

As you are Emailing your end users their assigned records, could you instead have your FE download that users open items each morning? Then they look at a local file to grab the record ID and only need to request the single record that they need work on?
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 16:30
Joined
Apr 27, 2015
Messages
6,286
When I saw your post I was going to suggest Albert Kallal's site, as well...it's really been the gold standard for solving this kind of problem.

Linq ;0)>

Quite right! Calling it the Gold Standard still doesn’t give it its due...
 

psyc0tic1

Access Moron
Local time
Today, 15:30
Joined
Jul 10, 2017
Messages
360
I don't like the thought of moving things from table to table. A status field you could filter on would be better in my view. Can they enter a project number (or the appropriate key field) instead of a date?
The records are already filtered by the status and the combo box only pulls records in a certain status.

Anyway I tried to see if it was the sheer amount of data in the data table causing the forms to load slow by deleting out all but about 20 records and the form still loads just as slow.

I then removed the 110 fields related to the USB Port data and that didn't help either.

Strange. It works fine locally and all other forms work fine split.

The Lab Test Input form is now the exact same as the Visual Inspecting input form other than filtering by status.
 

psyc0tic1

Access Moron
Local time
Today, 15:30
Joined
Jul 10, 2017
Messages
360
Anyway... for anyone interested in the solution to my problem.

I changed all the SQL statements in the forms to saved queries and for some reason there was a filter in the form that was causing the issue. I cleared the filter and BOOM!
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:30
Joined
Aug 30, 2003
Messages
36,118
Glad you got it sorted!
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 16:30
Joined
Apr 27, 2015
Messages
6,286
Anyway... for anyone interested in the solution to my problem.

I changed all the SQL statements in the forms to saved queries and for some reason there was a filter in the form that was causing the issue. I cleared the filter and BOOM!

Thanks for letting us hear the other shoe fall!
 

Users who are viewing this thread

Top Bottom