Access - most efficient way to locate records
Posted on 2013-05-28
I am writing a number of functions to check certain conditions in one of my tables and am looking for the most efficient way.
I have a 'milestone' table, which is related to 2 other key tables. I need to basically look to see if certain records either exist or meet certain conditions.
Is the following a good way to go about it?
1) I created a new Query containing all the key fields: project_ID, Part_ID, RevenueID, etc.
2) In the relevant function, I was thinking of creating a DAO recordset, and then using a select statement with the relevant subset/in the right order:
Set rstFrom = CurrentDb.OpenRecordset("SELECT * FROM … WHERE... ORDER BY …")
3) I can then use findfirst or scan through the records to test for certain conditions.
Is that the best way to go about it?