Feyo
asked on
Alternative to Find Method in VBA
I'm having a big problem with the Find Method in VBA with large recordsets. With small recordsets, the following codes works very well:
For Each vntAny In astrLabels
If vntAny <> "" Then
rstScores.MoveFirst
'Looking for a non-null value
rstScores.Find ("" & vntAny & " <> Null")
'If we find a non-null value, then we can move on to the rest of the code
If rstScores.EOF = False Then
rstScores.MoveFirst
'Looking for an "N"
rstScores.Find ("" & vntAny & " = 'N'")
'If we don't find the "N," we want that label in our final label array
If rstScores.EOF = True Then
astrLabelsKeep(intArrayPos
intArrayPos = intArrayPos + 1
End If
End If
End If
Next vntAny
With large recordsets, the above code crashes every time. Can anyone suggest modifications or alternatives? Thanks.
For Each vntAny In astrLabels
If vntAny <> "" Then
rstScores.MoveFirst
'Looking for a non-null value
rstScores.Find ("" & vntAny & " <> Null")
'If we find a non-null value, then we can move on to the rest of the code
If rstScores.EOF = False Then
rstScores.MoveFirst
'Looking for an "N"
rstScores.Find ("" & vntAny & " = 'N'")
'If we don't find the "N," we want that label in our final label array
If rstScores.EOF = True Then
astrLabelsKeep(intArrayPos
intArrayPos = intArrayPos + 1
End If
End If
End If
Next vntAny
With large recordsets, the above code crashes every time. Can anyone suggest modifications or alternatives? Thanks.
shanesuebsahakarn
I take it you are working with ADO recordsets?
ASKER
Yes, I am.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The Seek method in ADO requires that you use an index, a server-side cursor, and the recordset must have been opened using adCmdTableDirect as the CommandType. Also, I'm not sure if it would be possible to search for a NULL value, but experimentation will tell for sure. Your better option may to use SQL SELECT statements.
ASKER
I think you mentioned that yesterday or the day before, routinet. I've been messing around with them and it seems they are much more efficient, even opening and closing a whole bunch of times, than the find method. I'll keep at it to see for sure. Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Couldn't get the seek method to work for my purposes, but I learned alot in the process. I experimented and it is much faster than find. Can't verify that it will identify NULL values in an index field though. Thank you for the guidance.