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

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

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                
Loop

Open in new window


Any ideas on how I can further speed up the code?
0
avirocks12345
Asked:
avirocks12345
  • 2
  • 2
1 Solution
 
Aaron TomoskySD-WAN SimplifiedCommented:
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.
0
 
peter57rCommented:
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.
0
 
avirocks12345Author Commented:
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.
0
 
peter57rCommented:
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.
0
 
avirocks12345Author Commented:
Thank you peter! Works perfectly and makes perfect sense!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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