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
> Slow Form Load, Access 2016    
 
   
techexpressinc
post Jun 29 2020, 09:58 AM
Post#1



Posts: 495
Joined: 6-October 08
From: indiana, usa


One DB supported has a form that is for Reports. There is no processing (query) on the load of the form, the only event is the command to Maximize the screen.
However there are 48 buttons on the screen to generate a report. Each button has a macro that runs a query to pull data into Excel.

Is there a way to make the form load faster. Time to load is approx. 1-2 minutes.

Thanks,
Russ



--------------------
Thanks,
Russ Neuman, Insurance designations = FLMI, ASC, ALMI
Certified MS-Office Specialist
Go to the top of the page
 
theDBguy
post Jun 29 2020, 10:00 AM
Post#2


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


Are there any subforms or comboboxes or listboxes on it?

--------------------
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
 
GroverParkGeorge
post Jun 29 2020, 10:02 AM
Post#3


UA Admin
Posts: 37,510
Joined: 20-June 02
From: Newcastle, WA


I assume this form is unbound, correct?

If so, it's hard to think of a valid reason why it would be so slow to load unless it has a lot of combo or listboxes, and/or subforms on it. (As theDBGuy called out)
This post has been edited by GroverParkGeorge: Jun 29 2020, 10:03 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
kfield7
post Jun 29 2020, 03:05 PM
Post#4



Posts: 1,078
Joined: 12-November 03
From: Iowa Lot


As an alternative to 48 buttons, you might make a combo based on a table that enumerates and lists the reports, and the code to run the individual reports in a module.
This way you can also add/subtract reports without changing your form.

x=doMyReports(me.cbxReportSelection)


...

function doMyReports(ReportNum) as boolean

domyreports=true

select case reportNum

case 1
do report 1
case 2
etc.

case else
do my reports = false
end select


Go to the top of the page
 
techexpressinc
post Jun 29 2020, 03:43 PM
Post#5



Posts: 495
Joined: 6-October 08
From: indiana, usa


Thanks for the responses, here are the answers and attached a snapshot of the screen.

1 Are there any subforms or comboboxes or listboxes on it? no
2 I assume this form is unbound, correct? yes
3 listboxes, and/or subforms on it. no
4 change to a list enumerates and lists the reports - that would take some time

Russ

Attached File(s)
Attached File  TOP_QUARTER_OF_REPORTS_SCREEN_2020.jpg ( 80.89K )Number of downloads: 10
 

--------------------
Thanks,
Russ Neuman, Insurance designations = FLMI, ASC, ALMI
Certified MS-Office Specialist
Go to the top of the page
 
theDBguy
post Jun 29 2020, 04:35 PM
Post#6


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


Hi Russ. One more question. What kind of code are you running as the form opens/loads?

--------------------
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
 
tina t
post Jun 29 2020, 05:03 PM
Post#7



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


hi Russ, is there something fancy about your buttons - pictures on them, or something...?

QUOTE
4 change to a list enumerates and lists the reports - that would take some time

hmm, maybe not as much as you think. i use a table to display a reports menu to users. it has a few fields: a primary key Autonumber, a field for the "display" name of the report (what the user sees), and a field for the actual report name. i usually use a listbox control on an unbound form to display only the names of the reports to the user, while including all three fields in the control's RowSource property. so the code that runs in the listbox's AfterUpdate (or Click) event procedure is simple

DoCmd.OpenReport Me!MyListboxControlName.Column(2)

columns zero and 1 in the listbox, are respectively the pk and the "display" name. the only column that the user sees is column(1) the display name.

in your case, if you're outputting queries to Excel (rather than printing reports), just put the query name in the third field in the table, and run your OutputTo code referencing the appropriate listbox column, as above.

the nice thing about using a table is that you can add another column to identify what you want to do with each object, and still use minimal code. for instance, a fourth column that designates reports as 1, and queries as 2, allows you to include that (hidden) column in the listbox control, and then add a few more lines of code to handle it, as

CODE
If Me!MyListboxControlName.Column(3) = 1 Then
    DoCmd.OpenReport Me!MyListboxControlName.Column(2)
Else
    DoCmd.OutputTo acOutputQuery, DoCmd.OpenReport Me!MyListboxControlName.Column(2)...
End If

are the queries output to different filepaths? if they're standardized paths, add a table column to hold the paths, and incorporate that in your code. using a table gives you a lot of options to support various requirements, and it's a lot easier to update a table than to update forms and macros, or code. :)

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
techexpressinc
post Jun 30 2020, 08:04 AM
Post#8



Posts: 495
Joined: 6-October 08
From: indiana, usa


One more question. What kind of code are you running as the form opens/loads?
A button on the initial form opens the Reports form:
CODE
Private Sub cmdOpenReports_Click()
On Error GoTo Err_cmdOpenReports_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "frmSwitchBoard"
    DoCmd.Close acForm, stDocName, acSaveNo
    stDocName = "frmReports"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_cmdOpenReports_Click:
    Exit Sub
Err_cmdOpenReports_Click:
    MsgBox Err.Description
    Resume Exit_cmdOpenReports_Click
End Sub


2) "is there something fancy about your buttons - pictures on them, or something...?" No, standard buttons from Access
are the queries output to different filepaths? No, opens the Query in Excel, default directory Userid documents

Russ Question) maybe MS-Access is compiling all the Macros and attached Queries (40) on the screen on the open??
Attached is the macro code behind every button, the same code different query, they all run fast.
That would make some sense, I have seen MS-Access do activity like this, attached is a snapshot of of the Marco's behind a button.
If that is case the table/list driven reports would be fix for speeding up, 40+ reports and the screen labels do not match perfectly, the effort would be 20+ hours.




Attached File(s)
Attached File  BUTTON_THAT_OPENS_FORM_v6_30_2020.jpg ( 110.13K )Number of downloads: 1
Attached File  REPORT_SCREEN_DESIGN_VIEW_JUNE_2020.jpg ( 136.41K )Number of downloads: 1
Attached File  Macro_For_Each_Button_JUNE2020.jpg ( 36.63K )Number of downloads: 1
 

--------------------
Thanks,
Russ Neuman, Insurance designations = FLMI, ASC, ALMI
Certified MS-Office Specialist
Go to the top of the page
 
tina t
post Jun 30 2020, 03:03 PM
Post#9



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


QUOTE
maybe MS-Access is compiling all the Macros and attached Queries (40) on the screen on the open??

i have no idea if that happens or not, maybe somebody else will have some info. i wonder, though, if your form might be corrupted. you could create a new form and copy/paste the controls into it. i don't know how embedded macros work, whether they would copy over with the controls or not. anyway, worth a shot, maybe.

QUOTE
If that is case the table/list driven reports would be fix for speeding up, 40+ reports and the screen labels do not match perfectly, the effort would be 20+ hours.

i can't imagine it taking 20+ hours. creating the table would take maybe a minute. creating a new form and adding a listbox with the table as RowSource would take a few minutes, especially if you just copy over everything except the buttons from the existing form. writing the code, another minute or two.

that leaves populating the table. 48 rows shouldn't be too bad. for a recurring task, i'd probably write some code to get the info from the queries system table and maybe from the form's buttons' caption property. but this would be a one-off, so i'd probably just copy/paste the names of the queries into the table, and then just type in the "user-friendly" names, or copy/paste them from the buttons' caption property. maybe a couple hours work, to do everything.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
techexpressinc
post Jul 2 2020, 03:14 PM
Post#10



Posts: 495
Joined: 6-October 08
From: indiana, usa


Tina - thank you for your advice. I will consider a list for the report selection.

I might also try compiling the DB creating an Executable DB instead of .accdb a .accdbe, to see if that makes a faster load.

When I click on the button, and there is a 2 minute plus wait for the screen to load, that is not right, acceptable.

Russ



--------------------
Thanks,
Russ Neuman, Insurance designations = FLMI, ASC, ALMI
Certified MS-Office Specialist
Go to the top of the page
 
tina t
post Jul 2 2020, 04:36 PM
Post#11



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


hi Russ, you're welcome. did you try creating a new form, and copying your current objects into it, in case the form itself is corrupted?

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
AlbertKallal
post Jul 2 2020, 07:48 PM
Post#12


UtterAccess VIP
Posts: 3,102
Joined: 12-April 07
From: Edmonton, Alberta Canada


Ok, first a few things:

This slowdown is not the result of too many buttons, or graphics with the buttons.

And there is no out of the blue code or “too much” code that is running here.

I used Access for 20+ years and a form with 20 buttons and graphics or even a WHOLE boatload of code would never slow down a form that much.

My calendar form has 42 sub forms, and boatloads of code – it loads in one second.

This kind of delay is NOT buttons.
This kind of delay is not graphics on the form.
This kind of delay is not too much code that runs – since you not really running a lot of code on the form.

Sweep all those issues away!

This is a network issue, and one of 2-3 issues.

First, is this a standard office network, or is there some new VPN connection involved here?

Next up:

Can you launch the application (hold down shift key – allow NO CODE or ANY form to load.

Once you achieved the above?

Open a linked table – any table that is linked to the back end. Simply click on that table from the nav pane. Wait (and I betting it will take forever for that table view to open).

Once the table is open?

Minimise that table, or just from the navigation pane open your form that takes forever to load. (Or open the form with the button and try it).

Is your delay gone?

If yes, then this issue can be fixed by ensuring that you have a persistent connection on start-up.

If no?

Then next up, and near 99% of cases is one of two from this:

You have a linked table pointing to a non-existing back end or non-existing table. You want to delete this liked table.

Issue two?
Check your default printer. It is either a non-existing printer, or it is a defaulted network printer. Or even worse is a non-existing network printer.

Exit Access. Change your default printer to a LOCAL printer. If you don’t have a physical printer then use the print to PDF printer. You want a default printer as local. As noted, we often don’t have a real printer, so a local print to PDF OR print to XPS printer will suffice.

Now try the application.

So, this is not due to too many buttons. Never seen a form slowdown that much in 20 years of access.

This is not too much code either – never seen that as a problem.
Now, if start up code was pulling massive amounts of data from the back end, then ok, we have an issue – but that just not your case.

So, first up, is/are you working on a standard local network, or has some new VPN or some kind of network change occurred here?

We are assuming of course:
A standard split database.
A standard office network – LAN (local area network), and not some kind of over the internet or some kind of VPN is at play here.

So, try the persistent connection trick. (Open application with shift key by-pass. Open a linked table, minimize it. Now launch your form and try the button – is there a delay?

If above don’t fix/help/work?

Then change your default printer to a local one, and an existing one – not a network printer.

Again try the shift key by-pass, again try opening the linked table, and then again try lunching that form with the button.

Also, while you do these simple tests? Does the simply clicking on a linked table have a delay when you open that table to see and view the data?

My BEST bet for the money is a lisnked table that points to a non-existing back end. Next up a linked table to a non-existing table in the current back end.

And next best bet would be a non-existing default printer.

And I would also if all above fails then look at ANY report pointing to a non-existing printer.

Regards,

Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Go to the top of the page
 
cheekybuddha
post Jul 3 2020, 04:44 AM
Post#13


UtterAccess Moderator
Posts: 13,046
Joined: 6-December 03
From: Telegraph Hill


@Albert UPVOTE! thumbup.gif

--------------------


Regards,

David Marten
Go to the top of the page
 
techexpressinc
post Jul 6 2020, 02:04 PM
Post#14



Posts: 495
Joined: 6-October 08
From: indiana, usa


Albert - thank you for all your advice. I will start working through your suggestions and post feedback. Russ

--------------------
Thanks,
Russ Neuman, Insurance designations = FLMI, ASC, ALMI
Certified MS-Office Specialist
Go to the top of the page
 
tina t
post Jul 6 2020, 02:31 PM
Post#15



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


ditto the thanks, Albert! and for my part, i hope you continue to post your long answers - i appreciate your thorough explanations, and always learn a lot from them. :) tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    12th July 2020 - 08:47 PM