Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Problem loading access form

Posted on 2014-02-26
8
Medium Priority
?
619 Views
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.
0
Comment
Question by:bfuchs
[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
  • 4
  • 4
8 Comments
 
LVL 22

Expert Comment

by:rspahitz
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.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 39892858
Hi,

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 = " & me.id
now is based on the following:
SELECT PatientsScheduleChanges_HC.* FROM PatientsScheduleChanges_HC WHERE (((PatientsScheduleChanges_HC.PatientsEmployeesScheduleID)=[Forms]![PatientsEmployeesScheduleFrm].[id]));

Open in new window

0
 
LVL 4

Author Comment

by:bfuchs
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.
Doc4.doc
0
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.

 
LVL 22

Accepted Solution

by:
rspahitz earned 2000 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.
0
 
LVL 4

Author Closing Comment

by:bfuchs
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,

Thanks
Ben
0
 
LVL 22

Expert Comment

by:rspahitz
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???)
0
 
LVL 4

Author Comment

by:bfuchs
ID: 39899495
Hi,

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?

Thanks,
Ben
0
 
LVL 22

Expert Comment

by:rspahitz
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.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

618 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