Recordset search or DLookup

**Cobra17** · 12-22-2021, 05:42 PM

What's better/faster??

I imported a table from excel that I need to search by row for a variable, then search the heading names by another variable.

I use a recordset search (rst!) based on the rst!Type and rst!Size, then do a fields name loop (rst.Fields(x)) for a thickness variable ..

Please Login or Register  to view this content.

or I could just arrange the data so I can just do a DLookup

Please Login or Register  to view this content.

The DLookup is a lot cleaner code wise, but the table for the recordset looping is 'easier on the eyes'.... but what runs faster? Does it even matter with a table that is 48 rows by 35 Columns?

currently I have the recordset looping but figured i'd ask before I change my table.

**chocolate_kitten** · 03-19-2022, 12:17 PM

I've noticed that your table may be structured by using Thickness as column name, correct?
Instead of looping through field names as below,

Please Login or Register  to view this content.

You can just use Cost = rst.Fields(CStr(Thk))

In addition, we can assign a record set object with a SQL to have the return as a query instead of looping through the whole table.

Please Login or Register  to view this content.

To answer your question: Base on my experience, DLookup is more proper way to find a value in the database. However, it will see no difference on 48 rows of data.