Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 903
  • Last Modified:

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) = vntAny
               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.
0
Feyo
Asked:
Feyo
  • 3
  • 2
  • 2
2 Solutions
 
shanesuebsahakarnCommented:
I take it you are working with ADO recordsets?
0
 
FeyoAuthor Commented:
Yes, I am.
0
 
shanesuebsahakarnCommented:
You could try the Seek method - I haven't used it myself, but have a look at the documentation in the Help file - there's an example there.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Steve BinkCommented:
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.
0
 
FeyoAuthor Commented:
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.
0
 
Steve BinkCommented:
Seek is much more efficient than Find, since it relies on table indices instead of a 1-by-1 search of the data.  If used properly, you will no doubt see a very large performance benefit from using it over Find.  If you can verify that it will identify NULL values in an indexed field, please post that information.
0
 
FeyoAuthor Commented:
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.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now