VBA Access ADO: how to find a record position in the recordset

andy7789 used Ask the Experts™
Hi X-perts,

I am optimizing my code performance and have found a bottleneck (see the code attached). The idea is to get a relative position of a record (fundID) in the recordset sorted on various field.

The bad thing here is dumping data to a variant array and looping throughout this array.

How can I optimize this? Is there a way to get a record position position directly from a recordset without looping through?

Call Recordset.Open(SQL1, conHFI, CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockreadOnly, CommandTypeEnum.adCmdText)
        Recordset.Sort = "[ratio1] asc"
        FundData = Recordset.GetRows()
        FundsTotal = UBound(FundData, 2)
        'get rank
        For i = 0 To FundsTotal
            If (FundData(2, i) = fundID) Then GoTo A0

Open in new window

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
With Recordset
   .Sort = "[ratio1] asc"
   .FindFirst "FundData = " & fundID
   i = .AbsolutePosition + 1   'Add 1 to AbsolutePosition because it is zero-based
End With

Oops, I missed the array. I think you have to loop through the array to find the position. You could use split to convert the array to a recordset but I don't think that would be optimizing
Can you take a step back and explain what you are trying to do.
You can probably write a single query to do everything you want.
For starters from that code snippet, you could at least change your SQL to this:
    SELECT * FROM Table WHERE FindID = YourFundID ORDER BY Ratio ASC
That means SQL does a load of your processing for you.
But without the rest of your code, that SQL may not be suitable. So I need to know the big picture.
Basically writing line by line code to do the work of a single (100 x more efficient) query is a common mistake.


Thank you, guys. With small modifications thenelson's solution works great! Really boosting the performance on a subset of 6-8,000 records.

What I am doing here is simple: I have a matrix of 50 columns and 6,000+ rows. each row has its unique ID. I need to rank all IDs based on their place on each column after sorting the appropriate column.

I have another bottleneck of creating a temp table, which, I believe, could be improved. but it is another question - please, have a look at

As nmcdermaid suggested, this could probably be much further optimized but we cannot guess at what it would be since you only posted an incomplete piece of code. I agree with nmcdermaid that all this code probably could be replaced by a single query-statement.

BTW: It looks like you are naming a recordset "Recordset". Not a good idea as "Recordset" is an Access reserved word.  If you do use it, it should be in brackets so Access will not confuse it.

Reserved words in Microsoft Access 97:
List of reserved words in Access 2000:
List of reserved words in Access 2002 and Access 2003:

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial