• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

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.
0
Reborn84
Asked:
Reborn84
  • 3
  • 2
  • 2
  • +3
4 Solutions
 
nexusdsCommented:
i would check on your indexing and ensure you have good indexes in relation to your query.
0
 
jmoss111Commented:
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.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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)?

0
Independent Software Vendors: 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!

 
Reborn84Author Commented:
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.  
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Yes, multiple subforms definitely can take their toll on performance.

However, I'd encourage you to review your indexing, as suggested earlier. While indexing is as much art as it is science, the following are normally good candidates for an index:

Fields used in a WHERE clause
Fields used in a JOIN
Fields used in GROUP situation (query or report)
Fields used in a DISTINCT clause


Note also that while indexing can speed up retrieval of records, it can also slow data entry, so be aware of this.
0
 
Dale FyeCommented:

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.
0
 
clarkscottCommented:
I agree with only showing data that the user may need to see.  Adding a lot of subforms on a single form will run slow.
Does the user need to see all the subforms, or only selected data at any given time?  This is what you need to decide per your app.  Yeah.. it's cool to put all the data in a single form/subforms... but there's a price.

Scott C
0
 
Reborn84Author Commented:
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.  ;(
0
 
Dale FyeCommented:
Will they be editing the information in the subforms, or is it just there for viewing?

I'm not an expert on how subforms work, but my intuition tells me that Access is pulling all of the information (every record) for each of these subforms across your network, and then filtering for the appropriate record once the data is local.

If you were using a SQL Server backend, you could move away the linked master/child subform construct and base your subforms on more restrictive queries which would get processed on the server, and only return the values associated with your current record on the main form.  This would involve more coding, as you would have to initiate the requery of each of the subforms in the current event of the main form, and would have to add some code to the subforms to automatically fill in the field(s) that relate the subform to the main form (which is done automatically with link child/master subform).
0
 
Reborn84Author Commented:
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.
0

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!

  • 3
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now