Link to home
Start Free TrialLog in
Avatar of Reborn84
Reborn84

asked on

Improving speed or performance of opening record in form in Access

I currently have an Access database where the a user can search for a loan number and if that loan appear is present in the database the matching record displays on a subform.  The user can then double click on the loan number to open the record in a form (which is based on a query).  Basically, the double click event procedure opens the form and filters to the correct record using the code below:

DoCmd.OpenForm "frmqryData", , , "[Number]= '" & Me.[Number] & "'"

The issue I am having is now that the database contains almost 150k records, this process to open the record and can take 30-45 seconds.  Is there a faster way or better way to open the record in the form? The database is split to backend and front (linked tables) using Access 2003.
SOLUTION
Avatar of nexusds
nexusds

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ensure that you are not returning more data than is required for the query; if your index contained the required data to satisfy the query (called a covering index) then the query might not require a table scan  but rather an index scan to complete.
Avatar of Scott McDaniel (EE MVE )
Also, you could be dealing with network speed issues. Is this a typical LAN-type network? That is, in an office-type setting, with wired machines (no wireless, no VPN, no remote users, etc)?

Avatar of Reborn84
Reborn84

ASKER

I am returning all fiedls that are displayed on the form in my query.  The form does have mutliple subforms so I assume this slows the process down, but it has progressively worse as more records are added.  

The main table is indexed on the number field. Is there something else I can do?

The database is currently on a typical LAN - shared drive.  However, I recently converted the backend to SQL and use the Access front-end locally (whihch is what I am currently testing).  This worked for my other databases, but I did not see any perfromance improvement on this database.  
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial

Are all of the subforms displayed at the same time, or are they on tab controls, where you can only see one at a time.

In apps where I'm displaying subforms on tab controls, I will generally not even populate the subforms ControlSource until the tab controls Change event.  In there, I will check for the value of the tab control and then check to see whether the subform on that tab has a control source.  If not, I set the control source property.

I also put code in the current event of the form, that sets the ControlSource for each of the subforms to "" (except for my default tab.  Then I set the value of the tab control to my default tab.  This way, I only have to load one subform at a time, which will cause a slight delay as you move from one tab to the next, but it won't all be up front, so it is less noticeable.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I indexed all of the tables on the fields where they are joined, but that did not improve the performance.  It appears the subforms are my issue I have 8 subforms on the main form (yes I did say eight), but they only contain 2 fields each so I didn't think it was much of an issue.  The users does need to see all of these subforms at the same time.  I removed the subforms from the main form and all of the information pulls up instantly.  So I think loading all of those subforms is my demise.  

Thanks for all of the suggestions....if there are any more, I'd certainly appreciate it, but I think I already know the answer.  ;(
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The solution of indexing and that a subform will slow the performance was a valid solution.  Indexing was done, but not significantly increase performance due the number of subforms used.  Ultimately I found a way to integrate the data into the mian form and not use subforms.