+ Reply to Thread
Results 1 to 2 of 2

Dlookup too slow with external table, need to speed up or find alternate method

  1. #1
    Forum Contributor
    Join Date
    05-05-2009
    Location
    Wisconsin
    MS-Off Ver
    Excel 2010 and 365
    Posts
    109

    Dlookup too slow with external table, need to speed up or find alternate method

    I'm looking up three pieces of information in an external database based on a job number input on a form.


    Customer Name
    Part Number
    Part Name


    I have a macro attached to the after update event of the job number field so when someone enters the job number it looks up the information in the external table. I have the external table joined in access so it shows up in the table list. I use the setvalue and dlookup functions to set the value in each of the three fields individually (2 dlookup commands). It takes about 30 seconds to update.

    There must be a way to search for the job number once then return all three bits of data so I should at least be able to get this down into the 10 second range. That is still a little long though. Maybe I'm going about this completely wrong.


    I put the found data into unbound fields on the form.


    I tried creating a query based on the joined table. I filter out most of the records so that there are only about 100 records to search through but it didn't run any quicker.


    I am an access newbie so I'm hoping I'm just going about this the wrong way. Using Acess 2010.



    I'm not sure what is causing the delay. I normally use excel. Import into a table and only refresh as needed because the refresh is what takes the time. I was hoping that I could use a query to do something similar in Access. Don't refresh and run the query every time, only as needed.





    Ken

    Edit:


    Here's one of the dlookup statements


    Please Login or Register  to view this content.


    Update:

    To explain further...


    I have a form based on the master fixture list. This is where fixtures get input or updated or whatever. Select fixture then click on sign out fixture button. That opens a form based on the Signouts table and creates a new record.


    I created a query with both the Signouts table and the table that is linked to the external data. I joined the job number field in each. Include all from signouts table and only those from the thinked table that match. Then I based the form on the qurey. The proper information is pulled from the linked table (customer, part number, part name) when viewing the query but they don't show up in the fields that have the control sources listed as those fields. The text boxes just show #Name.

    Also, I can't add records which make the macro that opens the form and selects a new record now work.


    ========================

    Actually, now the fields do work. Not sure what was going on before. The text boxes on the form do pull the proper information. Still two problems though. 1. the first time I opened the form it took a minute and a half, probably to run the query. 2. I can't add or update records in the form.

    Next I think I will try a subform based on the linked table.



    ========================

    Subform actually seems to work. Almost. Takes about ten seconds to find data in subform. I could probably live with this if it was only triggered when actually inputting the job number but it takes 10 seconds just to advance to the next record.

    I'm come to the conclusion that I may need to deal with 10 seconds to find the data. I think I will go back to the original concept of Dlookup but figure out how to get all data with a single lookup. Maybe a function that will return record number so then I can use that record number to quickly get all three pieces of data?
    Last edited by Cyclops; 04-25-2013 at 02:11 PM.

  2. #2
    Forum Contributor
    Join Date
    05-05-2009
    Location
    Wisconsin
    MS-Off Ver
    Excel 2010 and 365
    Posts
    109

    Re: Dlookup too slow with external table, need to speed up or find alternate method

    OK. I think I might have made a little progress. It seems that the reason the lookup takes so long is because the field I'm looking up on isn't indexed.

    I imported the entire database rather than linking.

    Still took 10 seconds per lookup.


    I changed the index option on the lookup field.

    lookup is instantaneous.


    Now I just need to figure out how to make it so new data can be imported at the click of a button. Would be nice if it runs fast but it's only something that would need to be updated once or twice a day so even if it takes a minute or two.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1