• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Summary: How to optimize when stepping through a record set when using ADO (Using the collect metho

Summary: How to optimize when stepping through a record set when using ADO (Using the collect metho

Summary: How to optimize when stepping through a record set when using ADO (Using the collect method + more ¡.)

Rate Summary: How to optimize when stepping through a record set when using ADO (Using the collect metho

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

Private Sub LoadMyData()
 Dim my_Recs as Long, my_Counter as Long	
 With rs
   .Open "Select Name, Surname, Age from tblCust"
   If Not .EOF then
     my_Recs = .RecordCount
     For my_Counter = 1 To my_Recs
        If ISNULL(.Collect(0))=False Then
           Combo1.AddItem .Collect(0)
        Endif
       .MoveNext
     Next my_Counter
   EndIf	
   If .State = adStateOpen Then .Close
 End With

End Sub

'Few Notes
'This will not work with a Keyset cursor type
'The ADO Recorset object exposes 'a 'hidden, 'undocumented 'member: 'the 'Collect 'property. This 
'property acts similar as the Field's Value property, but it's faster 
'You do not need a reference to the Field object. 
'You can use this 'property by passing a numeric index or a field's name, as in:
'Example: Combo1.AddItem .Collect(0) instead of Combo1.AddItem .Collect("Name")
'Remeber that the field index starts at 0 for the first field
'ISNULL will filter any invalid data - Null is the same as Empty
'You can also use the .MoveLast before t_Recs = .RecordCount and .MoveFirst there after
'to ensure that you get the recordcount
'THIS CODE GIVE YOU PERFORMANCE ADVANTAGES ON THE FOLLOWING FRONTS
'Usage of the with block - decrease refrences
'Using the .Collect Method
'Using the for Loop aposed to the Do loop

'Note: Is also nice to use this code when you work with a Progress Bar.

'Adjust the code like this to accomidate the progress bar

Private Sub LoadMyData()
 Dim my_Recs as Long, my_Counter as Long	
 With rs
   .Open "Select Name, Surname, Age from tblCust"
   If Not .EOF then
     my_Recs = .RecordCount
     ProgressBar1.Max = my_Recs  'set the maximum value of progressbar
     ProgressBar1.Visable = False	
     For my_Counter = 1 To my_Recs
	ProgressBar1.Value = my_Counter
        If ISNULL(.Collect(0))=False Then
           Combo1.AddItem .Collect(0)
        Endif
       .MoveNext
     Next my_Counter
   EndIf
   ProgressBar1.Visable = False	
   If .State = adStateOpen Then .Close
 End With

End Sub