Problem loading access form

Posted on 2014-02-26
Last Modified: 2014-03-03
Hi all,

I have an access form that when I open it with all records it opens fine, however when i open it with a filter, requesting just one record it takes some significant time to open, Wondering what can i do to improve it?

The undderlined source is a linked sql table.

I did already the following

1- created an index on the field used to filter.
2- tried modified the record source of the form it should only contain the desired record.

So far none of these helped..

the table which this form is bound to contains about 35k records.
Question by:bfuchs
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
  • 4
  • 4
LVL 22

Expert Comment

ID: 39891031
Not sure of the answer, but there may be a few things you can do.

1) If the file size is very large, it may have some "un-emptied trash"  Go to the File/Office menu (Access 2007) and select Manage | Compact and Repair Database
This will make a copy of the database, cleaned up, and delete the original (the rename the copy so it looks like you have the original.)  Because of the reload, make sure to save everything before doing this, but it should be fine to do at any time (I've never seen it cause any problems, even if there's nothing much to do, and can compact the file by several megavbytes.)

2) Create a new query as a test just to see what happens if you try your filter query separate from the form.  Does it still have performance issues?

3) The index idea is great and you already did that.

4) Make a separate query and save it and see if you can apply that to the RecordSource of the form.  This may work or may be more hassles than it's worth if the filter criteria keeps changing.

5) How are you doing the filtering?  Maybe that process can be improved.

Author Comment

ID: 39892858

1- I do compact in regular basis, and also dont experience this in other forms that of this nature (that only contains data of one table, no joins, no subforms, no special controls like tabs images etc..)

2-in a query it does perform fine.

3-did that suggestion of assigning the query as the record source, however same issue.

4-tried first with docmd.openform "formname",,,"PatientsEmployeesScheduleID = " &
now is based on the following:
SELECT PatientsScheduleChanges_HC.* FROM PatientsScheduleChanges_HC WHERE (((PatientsScheduleChanges_HC.PatientsEmployeesScheduleID)=[Forms]![PatientsEmployeesScheduleFrm].[id]));

Open in new window


Author Comment

ID: 39892891
One interesting point,

the way it works now is that the form actually opens immeditely, and 70% of the controls get displayed as the forms open, then its starts spinning for a few seconds and only later is the other parts of the forms viewable.

Already tried playing around with the controls of that form, like hiding/removing them, at one point I only left one control on the screen to see if that helps but had the same issue.

attaching a screenshot of the form in discussion.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 22

Accepted Solution

rspahitz earned 500 total points
ID: 39893425
Since I see that Patient and Vendor are both pre-populated, have you looked at their queries to see if maybe the problem stems from those?

It could also be a hidden issue that Access is not showing.  You can try to rebuild the form to see if it solves the problem.  Create a blank form with the same Record Source (but no fields) to see how quickly it is.  Then grab a field (or a few at a time) and copy then paste onto the new form and test it.  Maybe one of the problem fields will reveal itself or maybe the new form will work fine and you can delete the old one.

Author Closing Comment

ID: 39893818
Hi rspahitz,
you got it!

It were those two combo that were causing the problem,
The reason I didn't think of looking into was that when opening the screen with all records it worked fine, if you have an axplanation to that woulg be great, however I am accepting your solution regargless,

LVL 22

Expert Comment

ID: 39895488
Not sure, but maybe there's a way to manage them separately, like leave it blank and populate it on Load, or apply a filter to it if it can be used based on some other field.
Other than that, maybe more computer memory or splitting the source table into smaller parts, but that's typically a drastic measure for something that might go away with a faster computer or an updated version (Access 2015???)

Author Comment

ID: 39899495

The reason it loaded slow is very simple, it was bound to a query that had to look for values of a very large table, with more then a half million records..
My question was just, why it did not cause any slowness when the form was simply open displaying all records, and when the form was opened with a filter, only containing one or two records, it did perform slow, taking this in account that not filter caused the problem just populating those combo boxes, don't it have to do it in both cases?

LVL 22

Expert Comment

ID: 39900919
Seems like it should be the same, but sometimes Access does things in its own way so maybe it starts loading that while it retrieves the full set of records and maybe that sequence causes it to process faster...who knows.  Sometimes caching can cause speedups (or slowdowns!) so it might be related to that too.  I guess that's a question for the Access development team at MS.
Anyway, I hope you found a way to improve the speed.

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

724 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