If your table is properly indexed (no duplicates) then you can skip checking to see IF he exists. Just add it and ignore the error generated by trying to add a duplicate record. That'll save you one read operation per loop.

It might also be faster to just use a query to pull down ALL of the records from the mainframe and then process it locally instead of doing read/write operations on a linked table. Use a local temp table.

As for your second point, my raw table is a local table.
So, if I skip the checking for the inmate to exist (my table is indexed correctly), should I have two complete loops, one to add and one to update?

One loop. Two recordsets.

I think I got what you are saying. Read one record from my raw data, and then add and update, ignoring errors.

Bingo

I gained about 10%.

Thanks

I'm fact, you're not doing any crazy calculations inside the loop, so why don't you just use an append query? It will probably run a lot faster.

Hi Sami, if AlphaRawT is a large table (hundreds of rows or more), then that DLookup() function will run hundreds of times or more, for every single row in AlphaRawT.  That seems like an area that could be optimized.  If it were me, I would add a Yes/No field to AlphaRawT to indicate if an inmate number exists or not, then run an update query to update that field.  That would find all the existing and non-existing inmate numbers in one go instead of using multiple DLookup() functions.

"Optimizing" usually means using as few table lookups (queries, recordsets, domain aggregate functions, etc.) as possible to do the same job.  That usually entails finding the ones with the most usages and reduce their use.

Kevin's idea is sound, provided you have access to modify that table. If you're pulling from a mainframe and you don't have the ability to do that, I'd say just use an append query. As long as your field is indexed (no dup) by that InmateNumber field, then you won't get duplicates and they'll just be ignored.

Thank you Kevin, but Richard is correct, I do not know what data I am getting before I receive the Raw file.

I will try the append query option this week.

Only downside with a query is that you can't see it running if it's a long process unless you use DoCmd.RunSQL and you'll get a progress bar.

Sami are you a CO?  My brother and father are in NY.

I am in CT, Connecticut.

We should get together next time you are in NY.

I work at West Point USMA.  Let connect this summer. How far are you from Orange County?

From my house to West Point is 124 miles, about a 2:15 drive. Not bad.

Yes, absolutely, let's connect.