Last Update: 2017 - 12- 27
Access/VBA - How to output data from an ADO-Recordset-Column as comma separated string using local sorting and filtering
Published: June 21st 2015
A couple of weeks ago I wrote an article about how to output the data of multiple rows of a column as a comma separated string by using DAO (Data Access Objects) and VBA.
The function I presented in that article queried the data source (table or query), concatenated the results in a string and returned that string. In an application with an Access-backend-database there is usually no hindrance to this approach and not much room to optimize it.
Additional factors to consider with remote data sources and ADO
If your application uses a Microsoft-SQL-Sever-Backend though and you use ADO (ActiveX Data Objects) to query the data, there might be some additional factors to consider. At this moment I see two reasons that would make the approach from the other article impossible or at least impractical.
OK, enough of introduction. Here comes the sample and the solution. The sample is based on a very simple table. The table is in a Microsoft SQL Server database, but the sample would be equally valid with any other remote database system.
So here is the somewhat constructed sample Stored Procedure written in TSQL that queries the backend and is our data source for this sample.
Note that I deliberately slowed this Stored Procedure down 5 seconds by adding the WAITFOR DELAY statement to simulate an expensive, longer running query operation. Furthermore I do not query the table directly but included a data transformation (the concatenation of the name) and store the transformation results in a temp table before returning the data from the temp table. This is just to make the point, that our client side function is independent of the underlying data structure on the server.
Let us assume, we use this Stored Procedure to query the data for a form or a list box-control on a form. So the Recordset returned by that Stored Procedure has already been created and is bound to a form (or list box) in our Access frontend application. Now we want to display this data in a comma separated string in another textbox control in our application.
A sample form that incorporates all this could look like this.
The code behind this form can be fairly minimal. As I considers this only to be supporting code in the context of this sample, I just include a screenshot here without comments and error handling. You absolutely should include the latter in any real world production application.
Implementation of the RSFieldAsSeparatedString-Function
Instead of querying the same data again from the backend, which is time consuming and wasteful, we use the Recordset already existing in the memory of the local machine and pass this Recordset to the function that transforms the data from one of the columns to a comma separated string.
The ADO-API has a powerful methods to help us solve this problem efficiently. You can use the Filter-Property of the ADO-Recordset to filter an existing Recordset with additional criteria and then you can use the Sort-Property to sort the Recordset for the purpose of creating the comma separated string. All this is based on the existing Recordset and happens without querying the data again.
As we use the existing Recordset elsewhere in our scenario, it is not advisable to apply the filter and sort properties to the original Recordset, as that would affect the display in the form (or list box) as well. Instead we use the Clone-Method of the ADO-Recordset inside the function to create a copy of the Recordset that we can filter and sort without affecting any other location in our application.
So based on all that explanations we could implement our function to process the column data to a comma separated string with VBA code like this:
Public Function RSFieldAsSeparatedString(ByVal fieldName As String, _ ByRef adoRS As ADODB.Recordset, _ Optional ByVal criteria As String = "", _ Optional ByVal sortBy As String = "", _ Optional ByVal delimiter As String = ", " _ ) As String ' Paramter description: ' fieldName = Is the name of the field containing the values ' we want in our comma separated string ' adoRS = Is the recordset containing the data ' criteria = The criteria to filter the data ' sortBy = An optional sort expression to sort the data ' delimiter = The delimiter used to separate the values. It defaults ' to a comma and a blank, but you can use anything you ' like there Dim retVal As String ' just a temporary buffer for the return value Dim adoRsClone As ADODB.Recordset ' the local clone of the recordset to process ' We don't the Filter and Sort properties to affect the original recordset. ' Therefore we clone the recordset an work with the clone. Set adoRsClone = adoRS.Clone With adoRsClone ' If there are additional criteria supplied, we use them for the filter property If Len(criteria) > 0 Then .Filter = criteria End If ' If there was a sort expression supplied, we use it for the sort property If Len(sortBy) > 0 Then .Sort = sortBy End If ' Just to make sure we start a the beginning of the recordset .MoveFirst Do Until .EOF ' we loop through the recordset and concatenate the values with the delimiter retVal = retVal & Nz(.Fields(fieldName).Value + delimiter, "") .MoveNext Loop ' we cut away the last delimiter retVal = Left(retVal, Len(retVal) - Len(delimiter)) ' setting the return value of the function RSFieldAsSeparatedString = retVal End With ' Closing and disposing the recordset Clone adoRsClone.Close Set adoRsClone = Nothing End Function
Using the function
In our sample form the call to achieve the result using this function is only the single line of code in the processData-method.
Private Sub processData() Me.txtCSOutput.Value = RSFieldAsSeparatedString( _ "ActorName", _ Me.lstAllActors.Recordset, _ "Rating>=" & Me.opgFilterRating.Value, _ IIf(Me.opgSortBy.Value = 1, "ActorName ASC", "Rating DESC")) End Sub
Of course this function would work on its own, without the assumed binding to a form/list box as well. You can just pass the Recordset returned by the GetActors-Function to the function creating the comma separated string.
Me.txtCSOutput.Value = RSFieldAsSeparatedString("ActorName", GetActors)
However if you need to apply significant filtering to the data, it would be more efficient to pass that filter criteria to the query (the Stored Procedure in this case) on the backend server and only pull the required data to the frontend. Yet in cases where you do not have the possibility to change or extend the server side logic and have to use existing Views and Stored Procedures, this approach is fully applicable.
OK, I think now we covered all the aspects of this topic I intended to deal with in this article. I hope you enjoyed reading this and hopefully got some inspiration from it on how to efficiently use ADO-Recordsets to do client side processing of data in your Microsoft Access application with VBA.
I you have got any comments on this, just send me quick mail.
© 1999 - 2017 by Philipp Stiefel