Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS Access Form loads slow

Posted on 2007-11-20
14
Medium Priority
?
633 Views
Last Modified: 2013-11-28
I have a MS Access 2003 Form that loads really slow.
On this form there is 4 field boxes that have queries behind them. On the entrance one query is running. The others run based on options chosen by the end user.

How can i make the form open up quickly? I've tried to turn subsheet off, seen there was something out there about making the form hidden and then not hidden. But only thing is everytime the end user logs in they need to go through a login process and then there is that query on the form it's trying to run but it can't because it's hidden.

Any answers to this would be really helpful. Thanks
0
Comment
Question by:jseaman12
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +1
14 Comments
 
LVL 11

Assisted Solution

by:Angelp1ay
Angelp1ay earned 1040 total points
ID: 20320928
Is the query massive?
Does it connect to an external data source over a slow network?
0
 
LVL 11

Assisted Solution

by:fanopoe
fanopoe earned 480 total points
ID: 20322046
Does the query run fast as a stand alone query?
If not, can you index the tables it is sourcing?
Are you using conditionals in your query (iif, dlookup, sum, etc...)? these can all slow a query down.
If the query runs fast and the form is still slow, are there a lot of graphics on the form that have to loaf? Id there a validation process that is happening as well?
you can put a debug.print statement in the form open event and put a breakpoint on that, then open your form and step through it. That should help spot the culprit, but I'd start with the query.

hth
0
 

Author Comment

by:jseaman12
ID: 20322288
The query is indexed very well, the query outside of the form only takes .02 seconds to load. It's only loading 3 fields out of 190 selections.

The Form takes forever to open.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 11

Assisted Solution

by:fanopoe
fanopoe earned 480 total points
ID: 20323144
can you step through the code using a breakpoint and find where it is hanging?
0
 

Author Comment

by:jseaman12
ID: 20323943
It's hanging on the opening of the form.
The query runs quickly.
0
 
LVL 11

Assisted Solution

by:Angelp1ay
Angelp1ay earned 1040 total points
ID: 20324617
How many controls do you have on the form?
Do you have any other code running before the query?
0
 

Author Comment

by:jseaman12
ID: 20325164
When you say Controls you mean buttons? If so there may be 10 buttons.
One Main query at the beginning. There are 3 other queries but they need to be initiated and at first there are 0 results.
0
 
LVL 20

Assisted Solution

by:clarkscott
clarkscott earned 480 total points
ID: 20326757
A control is ANYTHING on the form; text boxes, combos, labels, subforms, command buttons, etc.
If you have many of these the form will take longer to load.

Of course, all control record/row sources will add to the time.

Scott C
0
 
LVL 20

Assisted Solution

by:clarkscott
clarkscott earned 480 total points
ID: 20326763
also,
Hidden queries can be accessed by forms and code just as if they weren't hidden.  They are only hidden from users - not the app.

Does your form query contain many joins?  If so, and your data is large, this could be part of the issue.

Scott C
0
 
LVL 11

Assisted Solution

by:Angelp1ay
Angelp1ay earned 1040 total points
ID: 20326953
<< and at first there are 0 results >>
<< Does your form query contain many joins?  If so, and your data is large, this could be part of the issue. >>
This is a good point. Even if in the end you get no results it may take a while for Access to determine this!

You did say that the lag was before the query runs though. If you put a break point right at the start of your Form_Open event and another right at the start of your From_Load event can you identify where the lag is?
0
 

Author Comment

by:jseaman12
ID: 20327466
You know what I'm working on it now, I'm not 100% but there is a query I found on the opening form that is pulling a query that it shouldn't be. It's taking a long time and I'd bet this is why my form is taking forever to pull. I'll make sure that's correct and give points out for everyones help.
0
 

Author Comment

by:jseaman12
ID: 20327574
Update! Here is what was causing my issue.
I had a scenario where the end user would choose a particular id and a date range for that id.
Textbox had a query behind it that would call a SQL Passthrough and run with that.

What was occurring is that when the application was closed that query still resided in the pass through and the textbox would make that call and cause it to slip up again.

I'm going to wipe out all pass through queries that are affected with the form to save time on opening.

Thanks for your help everyone.

-Jeff
0
 
LVL 11

Accepted Solution

by:
Angelp1ay earned 1040 total points
ID: 20340323
So this is fixed?
0
 

Author Closing Comment

by:jseaman12
ID: 31410147
However it put my mindset in the right method and helped me figure out the solution. Thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question