Anytime you can reduce the number of times "d functions" have to go is going to speed things up. Just depends if you have to dlookup just as many times to load the array.

A record set may also be faster.

For example. The FacilityT has 15 records.
I need to do 12,000 the lookups for each inmate in the inmateT.

The next question is with the 15 Facility records loaded into an array, how do I go about retrieving the IDs? I need from the array?

Maybe I'm not understanding completely what you're trying to do, but actually why can't you just bring the 2 tables into a query and link them by the field that you're dlookuping? That would definitely be faster.

Can you not use a Query instead of a DLOOKUP?

How about a 2D-Array?

Kevin
A 2D array is what I was thinking about.
Now that it is loaded, How do I use it to pull the ideas? I want from it.?

Adam and Alex. I don't know why I did not consider a query. I will definitely try that in the morning.

@Sami I would steer clear of of the query idea, it would be very slow if done over and over again. Especially if you have to do it 12,000 times for each inmate record (I don't get that, but oh well).

@Sami, I do lots of reading and loading into arrays, things like colors to use, menu selections available, validation rules, all the controls on a form, etc. I tried collections, but they turned into a mess. I looked over Kevin R's code, and the only thing I would add is a module variable for the number of rows in the array, so that you can do a simple for...next through the array when needed, and make the PersonData array a module variable too (I'm not sure if one can ReDim a module array--I haven't done that). I have to find things similar to the ID/Code in arrays, and I just write a simple function to return the row position in the array (I think of columns like in a spreadsheet, fields; and use the module variable from above), and I specify which column to search for a value as an argument. VBA is super-fast with scanning an array for a value. Much faster than repeated DLookups and opening a .OpenRecordset again of needed later in the processing

Hi Sami, running DLookup 12k times is slow because it opens and closes the table 12k times.  To speed it up, open the table *just once* using OpenRecordset, then use FindFirst to do lookups.  Since you only have 15 records in your lookup table, FindFirst should be fast.  I did a test with this, and it only took 4 seconds to look up and update 12k rows.  This is the general strategy I use when I need to do table updates repeatedly: just open the necessary recordset variable and keep it open.

Hi Kevin Yip

Thank you for your input. This where I started with a recordset. I did not use FindFirst. it was still slow. I needed to tell the difference between new records and existing records. But I remembered Richard saying, don't fuss over this. run the record set twice, once for "Addnew" and one for "Edit" and use on error resume next.  That speeded up the process significantly.

Kevin, what syntax do you use for "FindFirst "?

Thanks

You use FindFirst on a recordset:

     Dim r As Recordset
     Set r = CurrentDb.OpenRecordset(" ... ")
     r.FindFirst "FirstName = 'Tom'"

Thank you Kevin

Friends

I managed, with your help, to reduce my daily processing from 45-60 minutes to under 9 minutes.

It is a multi-step update. I managed to optimize all the code with one exception.

I receive raw data from the Main Fraim every morning. it is an Inmate information file.
I have my InmateT in Access that I update from this CSV file (I import it to this temp AlphaRawT). My table has an InmateID (AutoNumber) the link to the external CVS file is the Inmate Number (ShortText).

The table has about 20K records about 10K are active.
this code to update the existing Inmates takes few seconds to run:

Details

    SQLStr = "UPDATE InmateT INNER JOIN AlphaRawT ON InmateT.InmateNumber = AlphaRawT.[Inmate Number]" & _
        " SET InmateT.InmateNumber = [AlphaRawT].[Inmate Number], InmateT.InmateName = [AlphaRawT].[Name (Full)]," & _
        " InmateT.FacilityCode = [AlphaRawT].[Facility Code], InmateT.Housing = [AlphaRawT].[Housing Unit Code]," & _
        " InmateT.HousingCode = [AlphaRawT].[Housing Code], InmateT.ReligionCode = [AlphaRawT].[Religion Affiliation Code]," & _
        " InmateT.IsActive = True , [AlphaRawT].AffiliationDate = [AlphaRawT].[AffiliationDate], [AlphaRawT].WorkCode = [AlphaRawT].[WorkCode]"

    CurrentDb.Execute SQLStr
    DoEvents

The problem is the following code that Appends new Inmates. It takes more than 7 minutes to add no more than 30 new inmates. The reason it takes so long is that it fails on most records because they already exist:

Details

    SQLStr = "INSERT INTO InmateT ( InmateNumber, FacilityCode, Housing, HousingCode, InmateName, ReligionCode, AffiliationDate, WorkCode )" & _
        " SELECT AlphaRawT.[Inmate Number], AlphaRawT.[Facility Code], AlphaRawT.[Housing Unit Code]," & _
        " AlphaRawT.[Housing Code], AlphaRawT.[Name (Full)], AlphaRawT.[Religion Affiliation Code], AlphaRawT.[AffiliationDate] ,AlphaRawT.[WorkCode]" & _
        " FROM AlphaRawT"

    CurrentDb.Execute SQLStr

I tried recordset and test if the inmate exist. that was very slow.

Can any of you suggest some thing that will speed this one INSERT sql?

many thanks

What ID field in InmateT matches in AlphaRawT?

Could just make a Query that finds all records in AlphaRawT that don't already exist in InmateT then you have your new Records.
It's Set Theory.

Thank you, Alex, for replying

InmateT is keyed in InmateID (AutoNumber), It has InmateNumber (ShortText) Indexed no duplicates.

InmateNumber is the field that matches in both tables : InmateT and AlphaRawT

I can do with your help on how to write the query you suggested.

many thanks

Thanks Alex

I used the wizard.
this will work wonders.

Many thanks

Thanks Alex

It ran in 3:12. huge improvement.

steps 6,7, and 8 process the InmateT in three passes. once I combine them into one pass, I should bring this down to 1 minute.

Thanks again.

Magic!

Maybe it's me, but somehow this post appears to have morphed quite a bit from the original post, where it was asked how to avoid repeated DLookups(). An array works best in my experience (as the problem was originally described). As to the morphed part...

Every week or month I would have to import thousands of "customer" and sales records from dozens of clients to update my database. Here's the sequence and tables I would use. Three tables are involved in each type of data to import.

1. Download the client's data into an "import table" (#1) (every client had different field formats). No mods are done to this data here, because I want the original as it was sent.
2. Convert the client downloaded table/records into my standardized intermediate import table (#2) (all the data is now in a field order and format that is standardized for my production database).
3. Run a program against all records in the intermediate table (#2) to identify changes, errors, new records, etc. (no updating to my production table (#3) yet, just comparison and checking).
4. Run an error report for the intermediate table. Get clients to repair data that doesn't make sense (like a sales record that doesn't have a corresponding "customer" record).
5. Update the #2 table to #3 table (my db's production table), there still may be errors (I have one program to do the error checking and updating. The only difference is that I can say do an error check pass {step #3 above}, and the update part wouldn't run as it does in this step).
6. Repeat steps 4 and 5 until all imported records have been processed.

In the intermediate table (#2), I would have a field called Processing status that I use for reporting and identifying situations, something like this:
Processing status (Ps):
0 = Record has been discarded after having been imported (it previously had one of the other Ps below)
1 = New import record, nothing done yet
2 = Error check routine, error found
3 = Error check routine, no errors found for this new "customer"
4 = Error check routine, no errors for this existing "customer"
5 = Record updated to the production table

Thank you Thomas