Problem loading access form

bfuchs used Ask the Experts™
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.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

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.

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

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.
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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.
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,


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???)

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?


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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial