Access/VBA - How to output data from an ADO-Recordset-Column as comma separated string using local sorting and filtering

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.

1. Your data is not pulled from a simple table or view, but is generated/queried by a stored procedure or stored function. You may still use the same approach as in the function, but it will not be possible to filter the data by simply using a where condition in the query.
2. It is very expensive and/or time consuming to query the data from the backend database and you are already displaying the same data in a form. Of course it would still work to use same approach here, but it is a waste of resources to query the same data twice.

Sample scenario

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:

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.

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.

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.

Share this article: