by NBS Solutions (5 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Thu 15th April 2004
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
Summary: How to optimize when stepping through a record set when using ADO (Using the collect method + more ยก.)
VBcoders
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
No comments have been posted about Summary: How to optimize when stepping through a record set when using ADO (Using the collect metho. Why not be the first to post a comment about Summary: How to optimize when stepping through a record set when using ADO (Using the collect metho.
