asked on

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?

Aaron Tomosky

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.

peter57r

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.

avirocks12345

ASKER

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.

ASKER CERTIFIED SOLUTION

peter57r

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

avirocks12345

ASKER

Thank you peter! Works perfectly and makes perfect sense!