Following-up on Creating ADODB Parameters on the fly and pushing the wrapping of ADODB a step further, I have written two more classes that allows me to expose methods that don't require a Connection object, without returning an ADODB.Recordset.Taking this method as a reference:Public Function Execute(connection As ADODB.connection, ByVal sql As String, ParamArray parametervalues()) As ADODB.Recordset    Dim Values() As Variant    Values = parametervalues    Dim cmd As ADODB.Command    Set cmd = CreateCommand(connection, adCmdText, sql, Values)    Set Execute = cmd.ExecuteEnd FunctionA bit of contextI'm not comfortable with the idea of exposing a method that would return an ADODB.Recordset without taking in an ADODB.Connection, because this would mean opening a connection in a function that doesn't control when the connection needs to be closed.To address this issue, I added two private fields to my SqlCommand:Private connString As StringPrivate resultFactory As New SqlResultI'm using a pre-determined connection string in Class_Initialize for the connString value:Private Sub Class_Initialize()    connString = Application.ConnectionStringEnd SubI adopted the Quick prefix to refer to an overload method that owns its own connection, hence the connection-less overload for the Execute method above will be called QuickExecute:Public Function QuickExecute(ByVal sql As String, ParamArray parametervalues()) As SqlResult    Dim parameters() As Variant    parameters = parametervalues    Dim connection As New ADODB.connection    connection.ConnectionString = connString    connection.Open    Dim rs As ADODB.Recordset    Set rs = Execute(connection, sql, parameters)    Set QuickExecute = resultFactory.Create(rs)    rs.Close    Set rs = Nothing    connection.Close    Set connection = NothingEnd FunctionThe method consumes the recordset and returns an object that encapsulates its contents, a SqlResult object.SqlResultThis type encapsulates a List<string> and a List<SqlResultRow> (see List class here), respectively holding field names and field values for each row.Property Item has a procedure attribute that makes it the type's default property, and a procedure attribute of -4 on property NewEnum allows iterating the SqlResultRow items with a For Each loop, like this:Dim sql As Stringsql = SELECT TOP 10 * FROM SomeTableDim cmd As New SqlCommandDim result As SqlResultSet result = cmd.QuickExecute(sql)Dim row As SqlResultRowFor Each row In result    Debug.Print row(SomeFieldName), TypeName(row(SomeFieldName))NextHere's the code:Private Type tSqlResult    FieldNames As List    Values As List    ToStringValueSeparator As StringEnd TypePrivate this As tSqlResultOption ExplicitPrivate Sub Class_Initialize()    Set this.FieldNames = New List    Set this.Values = New List    this.ToStringValueSeparator = ,End SubPublic Property Get ValueSeparator() As String    ValueSeparator = this.ToStringValueSeparatorEnd PropertyPublic Property Let ValueSeparator(ByVal value As String)    this.ToStringValueSeparator = valueEnd PropertyPublic Sub AddFieldName(name As String)    this.FieldNames.Add nameEnd SubPublic Function FieldNameIndex(ByVal name As String) As Long    FieldNameIndex = this.FieldNames.IndexOf(LCase$(name)) - 1End FunctionPublic Sub AddValue(value As SqlResultRow)    this.Values.Add valueEnd SubPublic Property Get Count() As Long    Count = this.Values.CountEnd PropertyPublic Property Get Item(ByVal index As Long) As SqlResultRow    Set Item = this.Values(index + 1)End PropertyPublic Property Get NewEnum() As IUnknown'Gets an enumerator that iterates through the List.    Set NewEnum = this.Values.NewEnumEnd PropertyPublic Function Create(adoRecordset As ADODB.Recordset) As SqlResult    Dim result As New SqlResult    Dim names As New List    Dim fieldValues As New List    Dim row As ADODB.fields    Dim field As ADODB.field    Dim rowFactory As New SqlResultRow    Dim grabFieldName As Boolean    grabFieldName = True    While Not adoRecordset.BOF And Not adoRecordset.EOF        For Each field In adoRecordset.fields            If grabFieldName Then result.AddFieldName LCase$(Coalesce(field.name, vbNullString))        Next        result.AddValue rowFactory.Create(result, adoRecordset.fields)        grabFieldName = False        adoRecordset.MoveNext    Wend    Set Create = resultEnd FunctionSqlResultRowEach row encapsulates an array of Variant values, and has an Item property (which also has a procedure attribute that makes it the type's default property) that can take either a String representing a field's name, or any number representing a field's index. A ToString method conveniently outputs all field values separated by commas (the actual separator is configurable in the SqlResult class).Private Type tRow    ParentResult As SqlResult    Values() As Variant    IsEmpty As BooleanEnd TypePrivate this As tRowOption ExplicitPrivate Sub Class_Initialize()    ReDim this.Values(0 To 0)    this.IsEmpty = TrueEnd SubPublic Property Set ParentResult(value As SqlResult)    Set this.ParentResult = valueEnd PropertyFriend Sub AddValue(ByVal value As Variant)    If Not this.IsEmpty Then ReDim Preserve this.Values(0 To UBound(this.Values) + 1)    this.Values(UBound(this.Values)) = value    this.IsEmpty = FalseEnd SubPublic Property Get Item(nameOrIndex As Variant) As Variant    If TypeName(nameOrIndex) = String Then        Item = GetFieldValueByName(nameOrIndex)    ElseIf IsNumeric(nameOrIndex) Then        Item = GetFieldValueByIndex(nameOrIndex)    Else        'return empty variant    End IfEnd PropertyPrivate Function GetFieldValueByName(ByVal name As String) As Variant    If Not this.IsEmpty Then GetFieldValueByName = this.Values(this.ParentResult.FieldNameIndex(name))End FunctionPrivate Function GetFieldValueByIndex(ByVal index As Integer) As Variant    If Not this.IsEmpty Then GetFieldValueByIndex = this.Values(index)End FunctionPublic Function Create(parent As SqlResult, fields As ADODB.fields) As SqlResultRow    Dim result As New SqlResultRow    Set result.ParentResult = parent    Dim field As ADODB.field    Dim value As Variant    For Each field In fields        If TypeName(field.value) = String Then            value = LTrim(RTrim(Coalesce(field.value, vbNullString)))        Else            value = Coalesce(field.value, vbEmpty)        End If        result.AddValue value    Next    Set Create = resultEnd FunctionPublic Function ToString() As String    If this.IsEmpty Then        ToString = TypeName(Me)        Exit Function    End If    Dim result As String    result = Join(this.Values, this.ParentResult.ValueSeparator)    ToString = resultEnd FunctionThe types are retained, so if a query returns a Date field, the type of that value will be Date in the SqlResultRow.I use a small helper function, Coalesce, to deal with null values. For reference, here's the listing:Public Function Coalesce(ByVal value As Variant, Optional ByVal value_when_null As Variant = 0) As Variant    Dim return_value As Variant    On Error Resume Next 'supress error handling    If IsEmpty(value) Or IsNull(value) Or (TypeName(value) = String And value = vbNullString) Then        return_value = value_when_null    Else        return_value = value    End If    Err.Clear 'clear any errors that might have occurred    On Error GoTo 0 'reinstate error handling    Coalesce = return_valueEnd Function
Materializing any ADODB Query
sql;vba;vb6;adodb
This loop (in SqlResult.Create):For Each field In adoRecordset.fields    If grabFieldName Then result.AddFieldName LCase$(Coalesce(field.name, vbNullString))Nextwill still iterate all fields even though grabFieldName is False. And since grabFieldName will only be True for the first record, why not just do it like this - and the flag should be called grabFieldNames, since the code is grabbing all field names:If grabFieldNames Then    For Each field In adoRecordset.fields        result.AddFieldName LCase$(Coalesce(field.name, vbNullString))    NextEnd IfSpeaking of AddFieldName, this implementation:Public Sub AddFieldName(name As String)    this.FieldNames.Add nameEnd SubMight work for most scenarios, but then if you want to have a Dictionary that maps field names to an index for more efficient field name lookups, a query like SELECT NULL AS Test, NULL AS Test will blow it up, since dictionary keys must be unique.Given this field (see Dictionary implementation here):Private nameIndices As New DictionaryAddFieldName could look like this:Public Sub AddFieldName(ByVal name As String)    Static nameInstances As New Dictionary    Dim localName As String    localName = LCase$(name)    If nameIndices.ContainsKey(localName) Then        If nameInstances.ContainsKey(localName) Then            nameInstances(localName) = nameInstances(localName) + 1        Else            nameInstances.Add localName, 1        End If        AddFieldName name & nameInstances(localName) 'recursive call    Else        this.FieldNames.Add localName        nameIndices.Add localName, this.FieldNames.Count - 1    End IfEnd SubThis way the first Test field will be called Test, and the 2nd one will be called Test1, ensuring uniqueness of the field names. This could be quite surprising to the calling code, though, but selecting identically named columns shouldn't happen very often.The FieldNameIndex function can then look like this:Public Function FieldNameIndex(ByVal name As String) As Long    Dim i As Long    If nameIndices.TryGetValue(name, i) Then        FieldNameIndex = i    Else        FieldNameIndex = -1    End IfEnd Function