Whenever you access a database field through the Recordset object you incur in a slight overhead. Take for example the following code snippet:
Dim rs As New ADODB.Recordsetrs.Open "SELECT au_lname, au_fname FROM authors", "DSN=pubs", , , adCmdTextDo Until rs.EOF List1.AddItem rs("au_lname") & ", " & rs("au_fname") rs.MoveNextLooprs.CloseThe references to the table’s fields inside the Do…Loop block is actually carried out by ADO as follows:
List1.AddItem = rs.Fields("au_lname") & ", " & rs.Fields("au_fname")and the overhead comes from the fact that ADO must locate the referenced fields in the Fields collection. You can make your code up to 3-4 times faster by explicitly creating Field objects before running the loop, as follows:
Dim rs As New ADODB.RecordsetDim fldFName As ADODB.Field, fldLName As ADODB.Fieldrs.Open "SELECT au_lname, au_fname FROM authors", "DSN=pubs", , , adCmdText' create the two Field objects that reference those two fieldsSet fldLName = rs.Fields("au_lname")Set fldFName = rs.Fields("au_fname")Do Until rs.EOF ' here you use the Field object's default Value property List1.AddItem fldLName & ", " & fldFName rs.MoveNextLooprs.CloseHowever, bear in mind that looping on all the records in a Recordset rarely is your fastest option. For example, in this particular case you can populate the ListBox control faster using the following approach:
Dim rs As New ADODB.RecordsetDim varArray() As VariantDim i As Long' let SQL Server concatenate the stringsrs.Open "SELECT au_lname+', '+au_fname FROM authors", "DSN=pubs", , , adCmdText' get all the strings in one operationvarArray() = rs.GetRows()' fill the ListBox controlFor i = 0 To UBound(varArray, 2) List1.AddItem varArray(0, i)Next
