How do I further optimize data loading to a form with unbound controls

Posted on 2011-05-08
Medium Priority
Last Modified: 2012-05-11
For very complicated reasons I'm developing an access 2007 database linked to a MySQL backend, which uses unbound controls which are populated via code. It pulls the data over with a pass-through query which is done very quickly. I've gotten the loading of the data from the query to the form down to about .05 seconds per control, which is fine for smaller forms, but you can imagine the exponential growth with larger forms. There are a few forms with upwards of 125+ controls.

I use the following code to load the data to the form:
Do Until counter = max + 1
     frm("d" & counter) = ELookup("f" & counter, "qryLoad_Data")
     counter = counter + 1                

Open in new window

Any ideas on how I can further speed up the code?
Question by:avirocks12345
  • 2
  • 2
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 35716006
Ideally you would do one query to get all the values and just assign them one at a time. Vba isn't my thing but I'm sure someone will have examples for you.
LVL 77

Expert Comment

ID: 35716010
I don't have any idea what Elookup() is but since it appears to be referring to a query I woud have thought that would impose a penalty everytime it is run.

Author Comment

ID: 35716043
Thanks for the responses!

Aaron, that's essentially what I've done. I run one pass-through query and assign the values one at a time.

Peter, Elookup is a user made function made by Allen Browne(See Here). Essentially it is an extended version of Dlookup and it increases the speed of data transfer to about .05 seconds per control from .07 seconds.
LVL 77

Accepted Solution

peter57r earned 2000 total points
ID: 35716219
Using Dlookup (or Elookup) you are running the query once for each field you fetch.

You need to be opening a recordset based on the query and so fetching the data once for all records you need.

Author Comment

ID: 35716345
Thank you peter! Works perfectly and makes perfect sense!

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

850 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