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

Posted on 2011-05-08
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
    LVL 38

    Expert Comment

    by: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.
    LVL 77

    Expert Comment

    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

    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

    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

    Thank you peter! Works perfectly and makes perfect sense!

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    26 Experts available now in Live!

    Get 1:1 Help Now