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

Set cmd = CreateCommand(connection, adCmdText, sql, Values)

Set Execute = cmd.Execute

End Function


### A bit of context

I'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 String
Private resultFactory As New SqlResult


I'm using a pre-determined connection string in Class_Initialize for the connString value:

Private Sub Class_Initialize()
connString = Application.ConnectionString
End Sub


I 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

connection.ConnectionString = connString

connection.Open

Set rs = Execute(connection, sql, parameters)

Set QuickExecute = resultFactory.Create(rs)

rs.Close
Set rs = Nothing

connection.Close
Set connection = Nothing

End Function


The method consumes the recordset and returns an object that encapsulates its contents, a SqlResult object.

### SqlResult

This 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 String
sql = "SELECT TOP 10 * FROM SomeTable"

Dim cmd As New SqlCommand
Dim result As SqlResult

Set result = cmd.QuickExecute(sql)

Dim row As SqlResultRow
For Each row In result
Debug.Print row("SomeFieldName"), TypeName(row("SomeFieldName"))
Next


Here's the code:

Private Type tSqlResult
FieldNames As List
Values As List
ToStringValueSeparator As String
End Type

Private this As tSqlResult
Option Explicit

Private Sub Class_Initialize()
Set this.FieldNames = New List
Set this.Values = New List
this.ToStringValueSeparator = ","
End Sub

Public Property Get ValueSeparator() As String
ValueSeparator = this.ToStringValueSeparator
End Property

Public Property Let ValueSeparator(ByVal value As String)
this.ToStringValueSeparator = value
End Property

End Sub

Public Function FieldNameIndex(ByVal name As String) As Long
FieldNameIndex = this.FieldNames.IndexOf(LCase$(name)) - 1 End Function Public Sub AddValue(value As SqlResultRow) this.Values.Add value End Sub Public Property Get Count() As Long Count = this.Values.Count End Property Public Property Get Item(ByVal index As Long) As SqlResultRow Set Item = this.Values(index + 1) End Property Public Property Get NewEnum() As IUnknown 'Gets an enumerator that iterates through the List. Set NewEnum = this.Values.NewEnum End Property Public 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

grabFieldName = False
Wend

Set Create = result

End Function


### SqlResultRow

Each 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 Boolean
End Type

Private this As tRow
Option Explicit

Private Sub Class_Initialize()
ReDim this.Values(0 To 0)
this.IsEmpty = True
End Sub

Public Property Set ParentResult(value As SqlResult)
Set this.ParentResult = value
End Property

Friend 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 = False

End Sub

Public 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 If

End Property

Private Function GetFieldValueByName(ByVal name As String) As Variant
If Not this.IsEmpty Then GetFieldValueByName = this.Values(this.ParentResult.FieldNameIndex(name))
End Function

Private Function GetFieldValueByIndex(ByVal index As Integer) As Variant
If Not this.IsEmpty Then GetFieldValueByIndex = this.Values(index)
End Function

Public Function Create(parent As SqlResult, fields As ADODB.fields) As SqlResultRow

Dim result As New SqlResultRow
Set result.ParentResult = parent

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
Next

Set Create = result

End Function

Public 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 = result

End Function


The 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_value

End Function

• You should try to avoid using SELECT * as it can slow down querying for no good reason. SELECT x,y,z is easier to understand and is as fast/faster than SELECT *. – PenutReaper Sep 19 '14 at 14:03
• Are you planning to add some filter or sort capabilities to your wrapper? – Unhandled Exception Sep 4 '17 at 13:17
• @UnhandledException why would I want to do that? If I need filtering I can have a WHERE clause in the query; if I need sorted results I can have an ORDER BY clause... Why not let the database do the hard work for me? – Mathieu Guindon Sep 4 '17 at 15:22
• Ok, maybe my intention is too specific. I use an ADODB-Recordset in VBA, which holds many records, which I have to process in different ways. To avoid to many roundtrips to the server I once load all necessary records and then use it offline and filter and sort it locally for each different analysation. It would be nice to get rid of this ADODB.Recordset at all and use a custom, more abstract, approach. Thats why I got the idea that your classes maybe could be enhanced to fulfill that. – Unhandled Exception Sep 4 '17 at 15:41
• They could... But I'm pretty sure doing that work in code would be much less efficient than having the database server do it. DB roundtrips aren't free indeed, but to me the costly part is iterating the returned results; the more you iterate them, the less efficient your code is. – Mathieu Guindon Sep 4 '17 at 16:01

I want to focus on the SqlResult/SqlResultRow classes here. The way it is, it is analogous to having bought a huge expensive truck then insisting on driving the original dinky car that you wouldn't trade in and paying the payments on both the truck and the dinky car.

Why?

Because you're basically taking an ADODB.Recordset object, a full-featured entity that provides sorting, filtering, jumping to an arbitrary position, and few more. That's your expensive truck. You then painstakingly copy the contents of the recordset into a custom collection which has much less features... that's your dinky car.

Now, you are doing this for encapsulation and that's not a bad thing at all! However, what I propose is that instead of copying the content from a recordset to a custom collection, that you use the ADODB.Recordset as the implementation underneath the SqlResult class.

That way, it becomes very easy to wrap methods like sorting, filtering, jumping what have you. The consumers of the SqlResult class need not know about the recordset under the hood driving the class.

But, I don't want the connection leaking!

And that's a legit concern! However, with an ADODB.Recordset, it is easy to manage this. What you actually want is a disconnected recordset. That way, the contents of the recordset are all available in the user's computer's memory and there's no dangling connection. What you should do is basically something like this:

Public Function Execute(connection As ADODB.connection, ByVal sql As String, ParamArray parametervalues()) As ADODB.Recordset

Dim Values() As Variant
Values = parametervalues

Set cmd = CreateCommand(connection, adCmdText, sql, Values)

'Configure the recordset to use client-side snapshot
'which is the only valid option for disconnected recordset
'It needs not be readonly but updatable disconnected recordset
'is needlessly complicating things anyway.
With rs
End With

'Load the recordset with result of the command
'We can't assign rs directly from the Execute method of the cmd
'or it'll coerce it to the wrong type of the recordset
rs.Open cmd

'Disconnect the recordset
Set rs.ActiveConnection = Nothing

Set Execute = rs

End Function


Now we have a disconnected recordset that can be browsed, iterated, etc. and then provided to the SqlResult class.

That way the consumers need not know about the implementation of ADO but you still get all the goodness of ADODB.Recordset without incurring any extra costs and you can then modify the SqlResult class to wrap various features on the ADODB.Recordset for essentially free. By the same token, SqlResultRow is easier, since you can leverage the ADODB.Record or something similar. Now you're actually driving that fancy expensive truck, something you would have gotten anyway even if you didn't really needed all the features it has to offer.

• I'm struggling to put this into use... "Cannot modify the ActiveConnection property of a Recordset object using a Command as a data source" on Set rs.ActiveConnection = Nothing – Mathieu Guindon Jun 14 '19 at 14:38
• It's a client-side static recordset, right? That's the only type that can be disconnected. – this Jun 14 '19 at 19:00

A quick code inspection with MZ-Tools reveals the following:

Local variables names, fieldValues and row can be safely removed from the Create method.

That's all the tool is picking up though.

I like how it makes everything automagical, however if it were the only way to get the data I'd be worried about performance with some large recordsets. The List class makes it easier to find a value by field name, but the search for the field name happens every time, which means lots of time is spent wasted finding the same field index over and over again, for each record. Keeping the index for each name in a Dictionary<String,int> would be more efficient than having to search for each column index for each row.

That said, SqlCommand has methods that take a ADODB.Connection and output a ADODB.Recordset, having the possibility to use these methods for larger recordsets and let the client code deal with the connection and the recordset, somewhat makes up for the performance hit of the wrapper SqlResult; you get the automagical parameters and the possibility to only iterate the data once.

This loop (in SqlResult.Create):

For Each field In adoRecordset.fields
If grabFieldName Then result.AddFieldName LCase$(Coalesce(field.name, vbNullString)) Next  will 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))
Next
End If


Speaking of AddFieldName, this implementation:

Public Sub AddFieldName(name As String)
End Sub


Might 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 Dictionary


AddFieldName 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
End If

AddFieldName name & nameInstances(localName) 'recursive call

Else
End If

End Sub


This 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 If

End Function


Is there any reason you don't use a disconnected record set and just close the connection in the function that opened it? I wouldn't keep a connection open any longer than you need.

<!doctype html>
<html>
<meta charset="utf-8">
<title>Untitled Document</title>

<body>
<p>This is a way I've found useful. The general idea is never keeping the connection open any longer than you have to. </p>
<pre>
Sub RunQuery()
'    You can declare as many arrays as you need
Dim RS1 As Variant
Dim ParameterValues As String
ParameterValues = "You can change this as needed"

RS1 = GetDiscRecordset(ParameterValues)

For c = LBound(RS1, 1) To UBound(RS1, 1)

For r = LBound(RS1, 2) To UBound(RS1, 2)

'    Iterate through the recordset
Debug.Print RS1(c, r)

Next r

Next c
End Sub
</pre>

<p>The <b>GetDiscRecordset</b> function is similar to your execute function but we are returning a <i>Disconnected</i> recordset.</p>
<pre>
Function GetDiscRecordset(ParameterValues As String) As Variant
Dim Qry As String

Qry = "Select * From SourceTable Where [?PlaceHolder for Parameters?]" 'Modify as needed

Qry = Replace(Qry, "[?PlaceHolder for Parameters?]", ParameterValues)

Conn.ConnectionString = "Connection String" 'Modify as needed

Conn.Open

Set Rst.ActiveConnection = Conn

'    Retrieve data

Rst.Open Qry, , , , adCmdText   '<- we set the rst stuff above so thats cool, thats our recordset

'    NOW DISCONNECT RECORDSET HERE!
Set Rst.ActiveConnection = Nothing

Rst.MoveFirst
'    Pass the recordset back
GetDiscRecordset = Rst.GetRows
End Function

</pre>
</body>
</html>

• Disconnected recordset is a very good point - if I wrote this today I'd scrap the SqlResult wrapper and return a disconnected recordset instead. The methods that take a connections parameter don't own it though, and thus shouldn't close it - they exist so that the code that owns the connection can initiate a transaction and run multiple commands before committing or rolling back. – Mathieu Guindon Oct 16 '18 at 20:40
• That said this looks more like a comment than an answer IMO. – Mathieu Guindon Oct 16 '18 at 20:41
• I agree with Mathieu here. As it stands this is not really an answer and more of a clarifying comment. If you could expand a bit on the point you're making and change the tone of the text to something more "answer"-y, that'd be appreciated. If you prefer to keep it like this, I can convert this to a comment for you. Just give me a heads up. Thanks! – Vogel612 Oct 16 '18 at 20:50