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.
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

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

Technology Partners: 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!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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…
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…

732 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