Need Advice on how to optimize search for records in a table

[SKV]

I have a table with 60k+ records. I have a VBA code for some function that search for records in this table so I use a WHILE LOOP. The problem is that this goes through each record of 60k+ rcds. So I was wondering if there is a way that search only go to specific (based on key field) records in the table.

Example: In the table below key field is (Field 1 + Field 2) i.e. Field 1 = X & Field 2 = A; there only 3 records with sequence nbrs 1, 5, 8.

Question: How can I make my code to jump directly to these rcds. The While loop or For loop goes and check each record for key thus takes a lot of time.

Seq	Field 1	Field 2	Val1
1	X	A	23
2	Y	1
3	F	R
4	F	R
5	X	A	34
6	G	H
7	Y	1
8	X	A	65

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

Please advise.

 

[jsotola]

it is not clear what you are searching for


are you searching for data in the last column?

or, do you already know the record numbers that you want to retrieve?

you can cut the search down by exiting the search routine once the record is found

also you can use adodb to retrieve the data

 

[jackd]

If you have created a compound index on field1 + field2, Access knows that.
When you create a query, part of the query optimizer within the database will identify whether indexes exist and if they should participate in your query. If the index is relevant it will be used.
So

SELECT * 
FROM myTable
WHERE
field1 & field2 = "XA"

will return records 1,5,8 and it will use your index