Problem loading access form

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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
bfuchsAuthor Commented:

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

bfuchsAuthor Commented:
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.
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
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???)
bfuchsAuthor Commented:

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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.