Does anyone know how access executes a FindFirst on a recordset that is based on a select query? Specifically, does Access start at the first record and move through sequentially until it finds a match or will it use a more efficient algorithm when searching an ordered field?
I would like to search through a UNION query and the field I am searching on will be composed of values from two separate tables. For example, say my two tables were Days and Months and I did a UNION of the two and sorted them by name. The result would be:
April
August
December
February
Friday
January
July
June
March
May
Monday
November
October
Saturday
September
Sunday
Thursday
Tuesday
Wednesday
If I did a FindFirst and looked for "September" would it start at April and go one at a time until it found September or would it use a more intelligent algorithm: for example, go to the middle record ("May"), see that September comes after May, then split the difference in the records in the second half of the database and get to September.
From the Access help, it appears a select query will make use of an indexed field from an underlying table, but it seems a UNION query might cause problems even if both fields were indexed in their respective tables.
I would prefer a link to some documentation that specifically addresses the issue, but I'd be glad to hear examples from other experts' experience. By the way, I am currently connecting to a Jet database, but will probably be moving the data into SQL server at some point in the future.
I look forward to the discussion.
-mike
Start Free Trial
