VBA - ADO - Recordset.GetRows method allows SQL column selection but CopyFromRecordset doesn't

I like ADO recordsets even though they are old school, they dovetail well with Excel VBA especially in that one can call the Range.CopyFromRecordset method and get a recordset written to a block of sheets in super quick time. However, in this post I show the Recordset.GetRows method which allows SQL column selection (technically known as projection) which is something lacking with CopyFromRecordset.

So the code below has two functions, the bottom function is just to create a test recordset because not everybody has a database lying around to which they can make queries, so there is nothing to see there. What there is to see is the top function which contains calls to the Recordset.GetRows method to return a rectangular two dimensional variant array which can easily be pasted onto a range of cells on a worksheet. If you are wondering why take two steps when CopyFromRecordset takes one then consider the parameters for CopyFromRecordset shown here immediately below

So in the first parameter Range.CopyfromRecordset takes a recordset and the remaining two parameters allow the rows and columns to be capped but there is no parameter by which to select the columns. But in the code below, specifically in the second call to GetRows we can specify a Fields parameter which is an array of field names. Cool.

Why does this matter (to me) ? Well, ADO recordsets are useful as 'state vehicles' or data marshalling devices in a distributed system. That is to say they can be used in network calls between separate machines on a network such as in a multi-tier (N-Tier) distributed system design pattern. Now that we know we can select columns we can engineer our system to be generous in columns knowing they can be selected out when writing to a worksheet.

Option Explicit

'* Tools -> References
'* Microsoft ActiveX Data Objects x.y Library

Function ProjectRecordset()
    Dim rstADO As ADODB.Recordset
    Set rstADO = CreateTestRecordset_NothingToSeeHere

    '* you need to move the cursor to first record
    rstADO.MoveFirst
    
    
    Dim vAllColumns As Variant
    vAllColumns = rstADO.GetRows()
    
    Debug.Assert UBound(vAllColumns, 1) - LBound(vAllColumns, 1) + 1 = rstADO.Fields.Count
    
    '* reset the cursor by moving it back to first record
    rstADO.MoveFirst
    
    '    ____      _   ____                         _                                   _           _   _ _
    '   / ___| ___| |_|  _ \ _____      _____    __| | ___   ___  ___   _ __  _ __ ___ (_) ___  ___| |_(_) ___  _ __  ___
    '  | |  _ / _ \ __| |_) / _ \ \ /\ / / __|  / _` |/ _ \ / _ \/ __| | '_ \| '__/ _ \| |/ _ \/ __| __| |/ _ \| '_ \/ __|
    '  | |_| |  __/ |_|  _ < (_) \ V  V /\__ \ | (_| | (_) |  __/\__ \ | |_) | | | (_) | |  __/ (__| |_| | (_) | | | \__ \
    ' (_)____|\___|\__|_| \_\___/ \_/\_/ |___/  \__,_|\___/ \___||___/ | .__/|_|  \___// |\___|\___|\__|_|\___/|_| |_|___/
    '                                                                  |_|           |__/
    
    
    Dim vSubselectionOfColumns As Variant
    vSubselectionOfColumns = rstADO.GetRows(, , Array("Animal", "ArrivalSequence"))

    Debug.Assert UBound(vSubselectionOfColumns, 1) - LBound(vSubselectionOfColumns, 1) + 1 = 2

    Dim rngDestination As Excel.Range
    'Set rngDestination = Workbooks("Foo").Worksheets("Bar").Range("a1")   '<---- Placeholder workbook and worksheet names
    'rngDestination.CopyFromRecordset  '<--- no parameter to select columns

    Stop  '* this is here so you can browse the Locals Window
    
End Function



Function CreateTestRecordset_NothingToSeeHere() As ADODB.Recordset

    '* Nothing to see here!  This is just some code to create a recordset out of thin air.
    '* Because not everybody has a database lying around to which they can make queries.
    '* The real lesson of this post is above in the GetRows method call
    
    Dim rstADO As ADODB.Recordset
    Dim fld As ADODB.Field
    '* Nothing to see here!
    Set rstADO = New ADODB.Recordset
    With rstADO
        '* Nothing to see here!
        .Fields.Append "Animal", adVarChar, 20
        .Fields.Append "BirthDay", adDate, FieldAttributeEnum.adFldKeyColumn
        .Fields.Append "ArrivalSequence", adInteger
    
        .CursorType = adOpenKeyset
        .CursorLocation = adUseClient
        .LockType = adLockPessimistic
        .Open
        
        .AddNew Array("Animal", "BirthDay", "ArrivalSequence"), Array("Cow", Now() - 200, 1)
        .AddNew Array("Animal", "BirthDay", "ArrivalSequence"), Array("Horse", Now() - 100, 2)
        .AddNew Array("Animal", "BirthDay", "ArrivalSequence"), Array("Pig", Now() - 150, 3)
        .AddNew Array("Animal", "BirthDay", "ArrivalSequence"), Array("Chicken", Now() - 120, 4)
        .AddNew Array("Animal", "BirthDay", "ArrivalSequence"), Array("Goat", Now() - 180, 5)
        .AddNew Array("Animal", "BirthDay", "ArrivalSequence"), Array("Dog", Now() - 140, 6)
        
    End With

    Set CreateTestRecordset_NothingToSeeHere = rstADO
    '* Nothing to see here!
End Function

OLEDB Simple Provider C# Custom Implementation

I am delighted to be able to say that I have succeeded in getting a C# Custom Implementation of a Data Source for use with the OLEDB Simple Provider. The code below demonstrates how to access in-memory arrays via ADO Recordsets for use in VBA clients..

This posts follows on from both the OLEDB Simple Provider OSP Toolkit Documentation post and the OLEDB Simple Provider - C++ Sample Step Thru post; together these form the investigation and documentation posts. I have been playing detective with this technology and have stepped through an old C++ sample, Googling for details as to how it works and accumulating documentation links before they fade forever. These have now given fruit in this post which shows how to achieve the same in C#.

The OLEDB Simple Provider

Writing a full OLEDB Provider is very hard, I've had some problems compiling even the free ATL/OLEDB sample that comes with Visual Studio 2017. A full OLEDB Provider requires the full implementation of a great many COM interfaces. Active Template Library (ATL) goes some way to give C++ default implementations of these interfaces but nevertheless OLEDB is complicated. As OLEDB is being replaced by .NET technologies, OLEDB is not worth hugely investing in.

So it is excellent news to know that there is the Simple Provider which provides a framework which a developer can customise by implementing only two (at minimum) interfaces, DataSource and OLEDBSimpleProvider. This is like being given a base class from which to inherit and override a minimum number of methods. Timesaver. Lifesaver.

C# Assembly Source Code

The following source code is for a .NET Framework Assembly (I built against .NET Framework 4.6.1) , register for COM interop and run Visual Studio under Administrator rights so it can access the registry.

Hard coded demonstration tables.

So I wanted to focus on the two interfaces that require implementing, DataSource and OLEDBSimpleProvider. To that end the code below does not connect to a database for its data, or open a file or read from a block of cells from an Excel worksheet. Instead to keep things simple, it initializes an in-memory array which is the case below is hard coded.

Clearly, the array could be dynamic as part of your own application. It should also be obvious that you can write your own code to open a file acting as a database.

The code below is also usefully annotated with links to Microsoft Docs.

C# Project requires references to COM libraries MSDATASRC and MSDAOSP

To make the code below compile you will need to add COM references to two separate type libraries. I give the file locations here below, though they may differ for your installation. You can also find them by looking through a VBA Tools->References dialog for there descriptions also given below.

C:\Windows\SysWOW64\msdatsrc.tlb - MSDATASRC - Microsoft Data Source Interfaces for ActiveX Data Binding Type Library
C:\Windows\SysWOW64\simpdata.tlb - MSDAOSP   - Microsoft OLE DB Simple Provider 1.5 Library

Once compiled and registered (don't forget Visual Studio needs Administrator rights to register your assembly in the registry), then you can run the sample VBA code given below (page/scroll down).

using MSDAOSP;
using MSDATASRC;
using System;
using System.Runtime.InteropServices;
using System.Collections.Generic;
using System.Threading;

// Added COM Reference to C:\Windows\SysWOW64\msdatsrc.tlb - MSDATASRC - Microsoft Data Source Interfaces for ActiveX Data Binding Type Library
// Added COM Reference to C:\Windows\SysWOW64\simpdata.tlb - MSDAOSP   - Microsoft OLE DB Simple Provider 1.5 Library

namespace SimpleOLEDBProvider1

{
    [ClassInterface(ClassInterfaceType.None)]
    [ComDefaultInterface(typeof(MSDATASRC.DataSource))]
    [ComVisible(true)]
    public class MyDataSource : MSDATASRC.DataSource
    {
        
        List<MSDATASRC.DataSourceListener> _listListeners = new List<MSDATASRC.DataSourceListener>();
        Dictionary<string, MyCustomRecordset> _dataMembers = new Dictionary<string, MyCustomRecordset>();


        ~MyDataSource()
        {   // release all listeners
            while (_listListeners.Count > 0)
            {
                MSDATASRC.DataSourceListener head = _listListeners[0];
                head = null; // calls IUnknown::Release (hopefully)
                _listListeners.RemoveAt(0);
            }
        }

        void MSDATASRC.DataSource.addDataSourceListener(DataSourceListener pDSL)
        {   // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms716931(v=vs.85)
            _listListeners.Add(pDSL);
        }

        dynamic MSDATASRC.DataSource.getDataMember(string bstrDM, ref Guid riid)
        {   // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms724549(v=vs.85)

            MyCustomRecordset retval = null;

            if (_dataMembers.ContainsKey(bstrDM))
            {
                retval = _dataMembers[bstrDM];
            }
            else
            {
                retval = new MyCustomRecordset();
                retval.Initialize(bstrDM);
                _dataMembers.Add(bstrDM, retval);
            }

            return retval;
        }

        string MSDATASRC.DataSource.getDataMemberName(int lIndex)
        {   // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms715947(v=vs.85)
            List<string> keys = new List<string>();
            keys.AddRange(_dataMembers.Keys);
            return keys[lIndex];
        }

        int MSDATASRC.DataSource.getDataMemberCount()
        {   // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms721173(v=vs.85)
            return _dataMembers.Count;
        }

        void MSDATASRC.DataSource.removeDataSourceListener(DataSourceListener pDSL)
        {   // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms724012(v=vs.85)
            _listListeners.Remove(pDSL);
        }
    }

    [ClassInterface(ClassInterfaceType.None)]
    [ComDefaultInterface(typeof(MSDAOSP.OLEDBSimpleProvider))]
    [ComVisible(true)]
    public class MyCustomRecordset : MSDAOSP.OLEDBSimpleProvider
    {
        private List<object[]> _tabularData = null;
        private string _bstrDM = null;
        private List<MSDAOSP.OLEDBSimpleProviderListener> _listListeners = new List<MSDAOSP.OLEDBSimpleProviderListener>();

        ~MyCustomRecordset()
        {
            while (_listListeners.Count > 0)
            {
                MSDAOSP.OLEDBSimpleProviderListener head = _listListeners[0];
                head = null; // calls IUnknown::Release (hopefully)
                _listListeners.RemoveAt(0);
            }
        }

        public void Initialize(string bstrDM)
        {
            //
            // we got some hard coded statrting tables for demonstration purposes only
            // in a real situation one would open a file or something
            //
            switch (bstrDM.ToLower())
            {
                case "customers":

                    _tabularData = new List<object[]>();
                    _tabularData.Add(new object[4] { "CustomerId", "CustomerName", "ContactName", "Country" });
                    _tabularData.Add(new object[4] { 1, "Big Corp", "Mandy", "USA" });
                    _tabularData.Add(new object[4] { 2, "Medium Corp", "Bob", "Canada" });
                    _tabularData.Add(new object[4] { 3, "Small Corp", "Jose", "Mexico" });

                    break;
                case "orders":

                    _tabularData = new List<object[]>();
                    _tabularData.Add(new object[3] { "OrderId", "CustomerId", "OrderDate" });
                    _tabularData.Add(new object[3] { 420, 2, new DateTime(2018, 10, 10) });
                    _tabularData.Add(new object[3] { 421, 3, new DateTime(2018, 10, 11) });
                    _tabularData.Add(new object[3] { 422, 1, new DateTime(2018, 10, 12) });
                    _tabularData.Add(new object[3] { 423, 2, new DateTime(2018, 10, 13) });

                    break;
                default:
                    // if unrecognised hand back default of colors
                    _tabularData = new List<object[]>();
                    _tabularData.Add(new object[2] { "ColorName", "ColorRGB" });
                    _tabularData.Add(new object[2] { "Red", "FF0000" });
                    _tabularData.Add(new object[2] { "Green", "00FF00" });
                    _tabularData.Add(new object[2] { "Blue", "0000FF" });
                    break;
            }

            _bstrDM = bstrDM;
        }

        void OLEDBSimpleProvider.addOLEDBSimpleProviderListener(OLEDBSimpleProviderListener pospIListener)
        {   // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms713697(v=vs.85)
            _listListeners.Add(pospIListener);
        }

        int OLEDBSimpleProvider.deleteRows(int iRow, int cRows)
        {   // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms713665(v=vs.85)

            // Notify our Listeners:
            foreach (OLEDBSimpleProviderListener listener in _listListeners)
                listener.aboutToDeleteRows(iRow, cRows);

            _tabularData.RemoveRange(iRow, cRows);

            // Notify our Listeners:
            foreach (OLEDBSimpleProviderListener listener in _listListeners)
                listener.deletedRows(iRow, cRows);

            return cRows;
        }

        int OLEDBSimpleProvider.find(int iRowStart, int iColumn, object val, OSPFIND findFlags, OSPCOMP compType)
        {   // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms709764(v=vs.85)
            // not yet implemented
            return -1;
        }


        int OLEDBSimpleProvider.getColumnCount()
        {   // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms715965(v=vs.85)

            object[] colHeaders = _tabularData[0];
            int totalColumnCount = colHeaders.Length;
            return totalColumnCount;
        }

        int OLEDBSimpleProvider.getEstimatedRows()
        {   // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms713703(v=vs.85)
            // should not include row header

            int totalRowCount = _tabularData.Count;
            return (totalRowCount - 1);
        }

        string OLEDBSimpleProvider.getLocale()
        {   // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms711216(v=vs.85)
            //return "en-us";
            var name = Thread.CurrentThread.CurrentCulture.Name;
            var locale = name.Split('-')[1];
            return locale;
        }

        int OLEDBSimpleProvider.getRowCount()
        {
            // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms715931(v=vs.85)
            // should not include row header

            int totalRowCount = _tabularData.Count;
            return (totalRowCount - 1);
        }

        OSPRW OLEDBSimpleProvider.getRWStatus(int iRow, int iColumn)
        {
            // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms709946(v=vs.85)
            return OSPRW.OSPRW_READWRITE;
        }

        dynamic OLEDBSimpleProvider.getVariant(int iRow, int iColumn, OSPFORMAT format)
        {
            // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms725341(v=vs.85)

            if (iRow < _tabularData.Count)
            {
                object[] row = _tabularData[iRow];
                return row[iColumn - 1];  // columns start at 1 it seems
            }
            else
            {
                // oddly, when a record is removed we still get called for dead row
                // so until we diagnose and fix this bug return a null
                return null;
            }
        }

        int OLEDBSimpleProvider.insertRows(int iRow, int cRows)
        {   // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms721207(v=vs.85)

            object[] colHeaders = _tabularData[0];
            int totalColumnCount = colHeaders.Length;

            object[] blankRow = new object[totalColumnCount];

            // Notify our Listeners:
            foreach (OLEDBSimpleProviderListener listener in _listListeners)
                listener.aboutToInsertRows(iRow, cRows);


            for (int i = 0; i < cRows; i++)
            {
                _tabularData.Insert(iRow, blankRow);
            }

            // Notify our Listeners:
            foreach (OLEDBSimpleProviderListener listener in _listListeners)
                listener.insertedRows(iRow, cRows);

            return cRows;
        }

        int OLEDBSimpleProvider.isAsync()
        {   // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms723042(v=vs.85)
            return 0; // not async
        }


        void OLEDBSimpleProvider.removeOLEDBSimpleProviderListener(OLEDBSimpleProviderListener pospIListener)
        {   // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms712993(v=vs.85)
            _listListeners.Remove(pospIListener);
        }

        void OLEDBSimpleProvider.setVariant(int iRow, int iColumn, OSPFORMAT format, object Var)
        {   // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms714366(v=vs.85)

            // Notify our Listeners:
            foreach (OLEDBSimpleProviderListener listener in _listListeners)
                listener.aboutToChangeCell(iRow, iColumn);

            object[] row = _tabularData[iRow];
            row[iColumn - 1] = Var;

            _tabularData[iRow] = row;

            // Notify our Listeners:
            foreach (OLEDBSimpleProviderListener listener in _listListeners)
                listener.cellChanged(iRow, iColumn);

        }

        void OLEDBSimpleProvider.stopTransfer()
        {   // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms722788(v=vs.85)
            // we do not handle async ops
        }
    }
}

VBA Client Code

So now you have compiled and registered above C# code now you can run client VBA code. You will need a reference to an ADO type library.

So to get an ADO recordset created by the C# custom implementation one does the following:

1. Create an ADO.Connection, supply the OLEDB Simple Provider and the ProgID of the C# custom implementation as Data Source;
2. Create a ADO.RecordSet, set the ActiveConnection property to that created in step (1);
3. Call the newly created recordSet's Open method supplying the table name.

In given example, the string passed into the RecordSet.Open method is a table name but one can write the code to parse and evaluate a more complicated expression.

Looking through the client code, you'll be able I have commented out some lines which call Recordset.Seek or Recordset.Sort methods because they are not supported and throw errors.

    'rsColors.Seek "Green"  '* throws error "Current provider does not support the necessary interface for Index functionality."
    'rsColors.Sort = "colorName" '* throws error "Current provider does not support the necessary interfaces for sorting or filtering."

But actually you can seek using the Recordset.Filter method. You can add and delete records happily, don't forget to call Recordset.Update. Code to add and delete records is given.

On the matter of sorting you could supply more text to the Recordset.Open method such as a 'Sort By <fieldName>' clause and change the implementation from being a List to a sortable collection class.

In fact you can supply so much more in the string passed to the Recordset.Open method. You could pass a whole SQL command just like other OLEDB providers but you will have to write your own SQL parsing code.

Also in the code below we take a snapshot of the recordset into a variant array. Snapshots are driven by the Recordset.GetRows method. This is also the same logic that drives the Range.CopyFromRecordset method which allows quick writing of a recordset to a block of cells (some commented out code for this is also given).

Enjoy!

Option Explicit

'* Tools->References
'ADODB      Microsoft ActiveX Data Objects 6.1 Library  C:\Program Files (x86)\Common Files\System\ado\msado15.dll

Sub TestCustomSimpleProvider()
    Dim vVarArray
    

    Dim oConn As ADODB.Connection
    Set oConn = New ADODB.Connection
    
    '*
    '* SimpleOLEDBProvider1.MyDataSource is progid of the custom simple provider
    '*
    oConn.Open "Provider=MSDAOSP;Data Source=SimpleOLEDBProvider1.MyDataSource"
    
    Dim rsColors As ADODB.Recordset
    Set rsColors = New ADODB.Recordset

    Set rsColors.ActiveConnection = oConn

    rsColors.Open "colors"
    
    '*
    '* what it doesn't do:
    '* (a) seeking on an index
    '* (b) sorting
    '*
    
    'rsColors.Seek "Green"  '* throws error "Current provider does not support the necessary interface for Index functionality."
    'rsColors.Sort = "colorName" '* throws error "Current provider does not support the necessary interfaces for sorting or filtering."
    
    '*
    '* what it does do:
    '* (1) filtering
    '* (2) adding
    '* (3) removing
    '*
    
    Debug.Assert rsColors.RecordCount = 3
    rsColors.Filter = "colorName='Green' or colorName='Red'"
    rsColors.Update
    Debug.Assert rsColors.RecordCount = 2
    
    rsColors.Filter = ""
    
    rsColors.AddNew
    rsColors!ColorName = "Yellow"
    rsColors!ColorRGB = "FFFF00"
    rsColors.Update
    
    Debug.Assert rsColors.RecordCount = 4
    
    rsColors.MoveFirst
    rsColors.Delete
    rsColors.Update
    
    Debug.Assert rsColors.RecordCount = 3
    
    
    rsColors.MoveFirst
    vVarArray = Application.WorksheetFunction.Transpose(rsColors.GetRows)
    Stop

    Dim rsCustomers As ADODB.Recordset
    Set rsCustomers = New ADODB.Recordset

    Set rsCustomers.ActiveConnection = oConn
    rsCustomers.Open "customers"

    vVarArray = Application.WorksheetFunction.Transpose(rsCustomers.GetRows)
    Stop
    
    Dim rsOrders As ADODB.Recordset
    Set rsOrders = New ADODB.Recordset
    
    Set rsOrders.ActiveConnection = oConn
    
    rsOrders.Open "Orders"
    vVarArray = Application.WorksheetFunction.Transpose(rsOrders.GetRows)

    '* uncomment the following to test writing whole recordset to worksheet in one go
    'Dim rng As Excel.Range
    'Set rng = ThisWorkbook.Worksheets.Item(1).Cells(1, 1)
    'rsOrders.MoveFirst
    'rng.CopyFromRecordset rsOrders
    
    Stop
End Sub

Links

• Excel Development Platform - OLEDB - C++ - Solved: ATL's OLEDB Provider sample crashes Excel (uncaught exception from msado15.dll)

OLEDB Simple Provider (OSP) Toolkit Documentation

Introduction

A StackOverflow question prompted me to look for an OLE DB Provider for Xml. This gave the top result of the Microsoft OLE DB Simple Provider | Microsoft Docs. There is some eye-catching text in its description that says

Simple providers are intended to access data sources that require only fundamental OLE DB support, such as in-memory arrays or XML documents.

I blogged the Xml aspect previously, so putting the Xml to one side, the text (I have bolded) says in-memory arrays. I have been looking for some kind of ADO interface for an in memory array for a little while. What is disappointing is that this technology is old so we cannot invest too much time in it. Nevertheless, I have done some surfing and am depositing some findings here on this post.

No Xml please, tell me about In-memory arrays

So I used Google to exclude Xml from results, Provider=MSDAOSP; in memory array -xml - Google Search.

From the link Implementing an ADO Server we can see that the interface guid (IID) is E0E270C0-C0BE-11D0-8FE4-00A0C90A6341}. Looking up this in the registry I find

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\Interface\{E0E270C0-C0BE-11D0-8FE4-00A0C90A6341}]
@="OLEDBSimpleProvider"

[HKEY_CLASSES_ROOT\Interface\{E0E270C0-C0BE-11D0-8FE4-00A0C90A6341}\ProxyStubClsid]
@="{00020424-0000-0000-C000-000000000046}"

[HKEY_CLASSES_ROOT\Interface\{E0E270C0-C0BE-11D0-8FE4-00A0C90A6341}\ProxyStubClsid32]
@="{00020424-0000-0000-C000-000000000046}"

[HKEY_CLASSES_ROOT\Interface\{E0E270C0-C0BE-11D0-8FE4-00A0C90A6341}\TypeLib]
@="{E0E270C2-C0BE-11D0-8FE4-00A0C90A6341}"
"Version"="1.5"

So in the above there is a type library guid (LIBID) so we can look for that and we find

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\TypeLib\{E0E270C2-C0BE-11D0-8FE4-00A0C90A6341}]

[HKEY_CLASSES_ROOT\TypeLib\{E0E270C2-C0BE-11D0-8FE4-00A0C90A6341}\1.5]
@="Microsoft OLE DB Simple Provider 1.5 Library"

[HKEY_CLASSES_ROOT\TypeLib\{E0E270C2-C0BE-11D0-8FE4-00A0C90A6341}\1.5\0]

[HKEY_CLASSES_ROOT\TypeLib\{E0E270C2-C0BE-11D0-8FE4-00A0C90A6341}\1.5\0\win32]
@="C:\\Windows\\SysWOW64\\simpdata.tlb"

[HKEY_CLASSES_ROOT\TypeLib\{E0E270C2-C0BE-11D0-8FE4-00A0C90A6341}\1.5\0\win64]
@="C:\\Windows\\System32\\simpdata.tlb"

[HKEY_CLASSES_ROOT\TypeLib\{E0E270C2-C0BE-11D0-8FE4-00A0C90A6341}\1.5\FLAGS]
@="0"

In the above we can see a path to the type library C:\\Windows\\SysWOW64\\simpdata.tlb . An excel workbook's VBA project can make a reference to this type library by selecting Microsoft OLE DB Simple Provider 1.5 Library because happily the type library is restricted to automation types; with a VBA reference one can inspect the type library via the Object Browser. However for fans of IDL and because it can be pasted as one long text document here is the IDL as given by OLEView.exe.

// Generated .IDL file (by the OLE/COM Object Viewer)
// 
// typelib filename: simpdata.tlb

[
  uuid(E0E270C2-C0BE-11D0-8FE4-00A0C90A6341),
  version(1.5),
  helpstring("Microsoft OLE DB Simple Provider 1.5 Library")
]
library MSDAOSP
{
    // TLib : OLE Automation : {00020430-0000-0000-C000-000000000046}
    importlib("stdole2.tlb");

    // Forward declare all types defined in this typelib
    interface OLEDBSimpleProviderListener;
    interface OLEDBSimpleProvider;

    typedef enum {
        OSPFORMAT_RAW = 0,
        OSPFORMAT_DEFAULT = 0,
        OSPFORMAT_FORMATTED = 1,
        OSPFORMAT_HTML = 2
    } OSPFORMAT;

    typedef enum {
        OSPRW_DEFAULT = 1,
        OSPRW_READONLY = 0,
        OSPRW_READWRITE = 1,
        OSPRW_MIXED = 2
    } OSPRW;

    typedef enum {
        OSPFIND_DEFAULT = 0,
        OSPFIND_UP = 1,
        OSPFIND_CASESENSITIVE = 2,
        OSPFIND_UPCASESENSITIVE = 3
    } OSPFIND;

    typedef enum {
        OSPCOMP_EQ = 1,
        OSPCOMP_DEFAULT = 1,
        OSPCOMP_LT = 2,
        OSPCOMP_LE = 3,
        OSPCOMP_GE = 4,
        OSPCOMP_GT = 5,
        OSPCOMP_NE = 6
    } OSPCOMP;

    typedef enum {
        OSPXFER_COMPLETE = 0,
        OSPXFER_ABORT = 1,
        OSPXFER_ERROR = 2
    } OSPXFER;

    [
      odl,
      uuid(E0E270C1-C0BE-11D0-8FE4-00A0C90A6341),
      version(1.4),
      oleautomation
    ]
    interface OLEDBSimpleProviderListener : IUnknown {
        HRESULT _stdcall aboutToChangeCell(
                        [in] long iRow, 
                        [in] long iColumn);
        HRESULT _stdcall cellChanged(
                        [in] long iRow, 
                        [in] long iColumn);
        HRESULT _stdcall aboutToDeleteRows(
                        [in] long iRow, 
                        [in] long cRows);
        HRESULT _stdcall deletedRows(
                        [in] long iRow, 
                        [in] long cRows);
        HRESULT _stdcall aboutToInsertRows(
                        [in] long iRow, 
                        [in] long cRows);
        HRESULT _stdcall insertedRows(
                        [in] long iRow, 
                        [in] long cRows);
        HRESULT _stdcall rowsAvailable(
                        [in] long iRow, 
                        [in] long cRows);
        HRESULT _stdcall transferComplete([in] OSPXFER xfer);
    };

    [
      odl,
      uuid(E0E270C0-C0BE-11D0-8FE4-00A0C90A6341),
      version(1.4),
      oleautomation
    ]
    interface OLEDBSimpleProvider : IUnknown {
        HRESULT _stdcall getRowCount([out, retval] long* pcRows);
        HRESULT _stdcall getColumnCount([out, retval] long* pcColumns);
        HRESULT _stdcall getRWStatus(
                        [in] long iRow, 
                        [in] long iColumn, 
                        [out, retval] OSPRW* prwStatus);
        HRESULT _stdcall getVariant(
                        [in] long iRow, 
                        [in] long iColumn, 
                        [in] OSPFORMAT format, 
                        [out, retval] VARIANT* pVar);
        HRESULT _stdcall setVariant(
                        [in] long iRow, 
                        [in] long iColumn, 
                        [in] OSPFORMAT format, 
                        [in] VARIANT Var);
        HRESULT _stdcall getLocale([out, retval] BSTR* pbstrLocale);
        HRESULT _stdcall deleteRows(
                        [in] long iRow, 
                        [in] long cRows, 
                        [out, retval] long* pcRowsDeleted);
        HRESULT _stdcall insertRows(
                        [in] long iRow, 
                        [in] long cRows, 
                        [out, retval] long* pcRowsInserted);
        HRESULT _stdcall find(
                        [in] long iRowStart, 
                        [in] long iColumn, 
                        [in] VARIANT val, 
                        [in] OSPFIND findFlags, 
                        [in] OSPCOMP compType, 
                        [out, retval] long* piRowFound);
        HRESULT _stdcall addOLEDBSimpleProviderListener([in] OLEDBSimpleProviderListener* pospIListener);
        HRESULT _stdcall removeOLEDBSimpleProviderListener([in] OLEDBSimpleProviderListener* pospIListener);
        HRESULT _stdcall isAsync([out, retval] long* pbAsynch);
        HRESULT _stdcall getEstimatedRows([out, retval] long* piRows);
        HRESULT _stdcall stopTransfer();
    };
};

So in the interface definition we can see some usefully unique keywords which we can use a search terms to search for sample code. And we find a good but old volume Serious ADO: Universal Data Access with Visual Basic - Rob MacDonald - Google Books which gives us some hints about how to write a VB6 component but it becomes apparent that one needs an actual VB6 copy because there is a widget to implement an interface that is otherwise restricted to VB6 developers. I have tracked the type library

MSDATASRC     Microsoft Data Source Interfaces for ActiveX Data Binding Type Library     C:\Windows\SysWOW64\msdatsrc.tlb

And I give the type library's IDL

// Generated .IDL file (by the OLE/COM Object Viewer)
// 
// typelib filename: msdatsrc.tlb

[
  uuid(7C0FFAB0-CD84-11D0-949A-00A0C91110ED),
  version(1.0),
  helpstring("Microsoft Data Source Interfaces for ActiveX Data Binding Type Library")
]
library MSDATASRC
{
    // TLib : OLE Automation : {00020430-0000-0000-C000-000000000046}
    importlib("stdole2.tlb");

    // Forward declare all types defined in this typelib
    interface DataSourceListener;
    interface DataSource;

    typedef [uuid(7C0FFAB1-CD84-11D0-949A-00A0C91110ED), public]
    BSTR DataMember;

    [
      odl,
      uuid(7C0FFAB2-CD84-11D0-949A-00A0C91110ED),
      hidden,
      oleautomation
    ]
    interface DataSourceListener : IUnknown {
        [hidden]
        HRESULT _stdcall dataMemberChanged([in] DataMember bstrDM);
        [hidden]
        HRESULT _stdcall dataMemberAdded([in] DataMember bstrDM);
        [hidden]
        HRESULT _stdcall dataMemberRemoved([in] DataMember bstrDM);
    };

    [
      odl,
      uuid(7C0FFAB3-CD84-11D0-949A-00A0C91110ED),
      oleautomation
    ]
    interface DataSource : IUnknown {
        [restricted, hidden]
        HRESULT _stdcall getDataMember(
                        [in] DataMember bstrDM, 
                        [in] GUID* riid, 
                        [out, retval] IUnknown** ppunk);
        [hidden]
        HRESULT _stdcall getDataMemberName(
                        [in] long lIndex, 
                        [out, retval] DataMember* pbstrDM);
        [hidden]
        HRESULT _stdcall getDataMemberCount([out, retval] long* plCount);
        [hidden]
        HRESULT _stdcall addDataSourceListener([in] DataSourceListener* pDSL);
        [hidden]
        HRESULT _stdcall removeDataSourceListener([in] DataSourceListener* pDSL);
    };
};

So the restricted method on the interface DataSource prevents VBA from implementing this interface. Any source code for VB6 would rely upon a widget. This means that only the Delphi article, Implementing an ADO Server and any C++ source code we can find can give us a clue as to how this can be implemented. It ought to be possible to implement in C#.

API Links

MSDATASRC.DataSource Interface Methods

DataSource Interface and Methods is defined in MSDATASRC namespace as imported by reference to C:\\Windows\\SysWOW64\\msdatsrc.tlb

• addDataSourceListener
• getDataMember
• getDataMemberCount
• getDataMemberName
• removeDataSourceListener

MSDATASRC.DataSourceListener Interface Methods

DataSourceListener Interface and Methods is defined in MSDATASRC namespace as imported by reference to C:\\Windows\\SysWOW64\\msdatsrc.tlb

• dataMemberAdded
• dataMemberChanged
• dataMemberRemoved

MSDAOSP.OLEDBSimpleProvider Interface Methods

OLEDBSimpleProvider Interface and Methods are defined in MSDAOSP namespace as imported by reference to C:\\Windows\\SysWOW64\\simpdata.tlb

• addOLEDBSimpleProviderListener
• deleteRows
• find
• getColumnCount
• getEstimatedRows
• getLocale
• getRowCount
• getRWStatus
• getVariant
• insertRows
• isAsync
• removeOLEDBSimpleProviderListener
• setVariant
• stopTransfer

MSDAOSP.OLEDBSimpleProviderListener Interface Methods

OLEDBSimpleProviderListener Interface and Methods are defined in MSDAOSP namespace as imported by reference to C:\\Windows\\SysWOW64\\simpdata.tlb

• aboutToChangeCell
• aboutToDeleteRows
• aboutToInsertRows
• cellChanged
• deletedRows
• insertedRows
• rowsAvailable
• transferComplete

Other OLEDB Links

• OLE DB Simple Provider (OSP) Toolkit
• OLE DB Programmer's Guide Appendix G: OLE DB Methods to Interfaces Table
• OSP Toolkit Files and Redistribution: File Listing
• OSP Language Reference
• OSP: Limitations
• OSP: Building a Simple Provider
• OSP: Toolkit Components
• OSP: Notifications
• OSP: Using the RegisterProvider.reg File
• OSP: Full Registration
• OSP: msDataSourceObject
• Visual Basic: Code Sample Information

Other Links

• Microsoft OLE DB Simple Provider | Microsoft Docs
• Provider=MSDAOSP; in memory array -xml - Google Search
• Implementing an ADO Server
• OLE DB Providers
• DSO(data source object) Interop problem in IE | PC Review
• getRWStatus "getVariant" - Google Search
• Serious ADO: Universal Data Access with Visual Basic - Rob MacDonald - Google Books

VBA - Fabricate an ADO Recordset for Sorting and Filtering

In VBA we can fabricate an ADO Recordset without any database whatsoever. Then we can take advantage of filtering and sorting.

This is in response to a Stack Overflow question - How to sort a subset according to some ordered superset?

Sub Test()

    Dim rstADO As ADODB.Recordset
    Dim fld As ADODB.Field

    Set rstADO = New ADODB.Recordset
    With rstADO
        .Fields.Append "Animal", adVarChar, 20
        .Fields.Append "BirthDay", adDate, FieldAttributeEnum.adFldKeyColumn
        .Fields.Append "ArrivalSequence", adInteger
    
        .CursorType = adOpenKeyset
        .CursorLocation = adUseClient
        .LockType = adLockPessimistic
        .Open
        
        .AddNew Array("Animal", "BirthDay", "ArrivalSequence"), Array("Cow", Now() - 200, 1)
        .AddNew Array("Animal", "BirthDay", "ArrivalSequence"), Array("Horse", Now() - 100, 2)
        .AddNew Array("Animal", "BirthDay", "ArrivalSequence"), Array("Pig", Now() - 150, 3)
        .AddNew Array("Animal", "BirthDay", "ArrivalSequence"), Array("Chicken", Now() - 120, 4)
        .AddNew Array("Animal", "BirthDay", "ArrivalSequence"), Array("Goat", Now() - 180, 5)
        .AddNew Array("Animal", "BirthDay", "ArrivalSequence"), Array("Dog", Now() - 140, 5)
        
        
        .Filter = "Animal='Cow' or Animal='Dog' or Animal='Pig'  or Animal='Horse'"
        
        Dim vSnap As Variant
        .MoveFirst
        vSnap = .GetRows
        
        Debug.Assert vSnap(0, 0) = "Cow"
        Debug.Assert vSnap(0, 1) = "Horse"
        Debug.Assert vSnap(0, 2) = "Pig"
        Debug.Assert vSnap(0, 3) = "Dog"
        
        
        '*
        '* Now sort according to birthday
        '*
        .Sort = "BirthDay"
        
            
        Dim vSnap2 As Variant
        .MoveFirst
        vSnap2 = .GetRows
        
        Debug.Assert vSnap2(0, 0) = "Cow"
        Debug.Assert vSnap2(0, 1) = "Pig"
        Debug.Assert vSnap2(0, 2) = "Dog"
        Debug.Assert vSnap2(0, 3) = "Horse"
            
            
    End With

End Sub

Links

• arrays - How to sort a subset according to some ordered superset? - Stack Overflow

OLEDB Simple Provider - C++ Sample Step Thru

It is with delight that I can report that I found a OLEDB Simple Provider C++ sample that Microsoft had deposited in Github for posterity. I got it working with a little tweak and was able to write some client VBA ADO code to retrieve an ADO recordset. I also managed to step through the code to document the interaction.

Context - Data Access Solutions

I have been surveying data access solution for working with Excel worksheets. I have found the Microsoft.ACE.OLEDB provider but I have found some weaknesses with it and was wondering what else is possible. As a caveat, it is worth noting Microsoft say they will remove this feature (OLEDB Simple Provider) from a future version of Windows. Nevertheless, for due diligence this solution ought to be explored (later I intend to investigate .NET managed data providers which will be more future proof).

Context - OLEDB Simple Provider VB6 sample

Also note that I have been investigating trying to write a custom implementation for the OLEDB Simple Provider using VBA. VBA is extremely close to VB6. Unfortunately, VB6 has a few extra mechanisms to assist the interaction with the underlying OLEDB Simple Provider interfaces. These extra mechanisms are simply not present in VBA rendering any VB6 code informative but unusable. So I needed to step through the C++ sample because I want to write a C# implementation.

Because a VBA solution is not possible we need to drop down to lower level, C++, so we can figure out how to write a C# implementation.

Client VBA Code

Below is ordinary client VBA ADO code. The key details are in the ADO connection string and the string passed to ADO recordset's Open method.

The connection is critical to correctly using the OLEDB Simple Provider. First, we first specify Provider=MSDAOSP; in all cases. The second term Data Source is where we specify a COM ProgId of a COM class which implements IDataSource. In this case we pass Data Source=ospsampc because ospsampc is the ProgId of the Windows sample. The registry settings can be found in Appendix A .

The other key detail is what is passed to ADO recordset's Open method. In the sample a file path is passed. However, there is no reason why a different implementation may pass a web url or a COM moniker. In the sample code, the file is opened and processed; it is semicolon separated with a row and column count on the top row (see Appendix B).

Option Explicit

'* Tools->References
'ADODB      Microsoft ActiveX Data Objects 6.1 Library  C:\Program Files (x86)\Common Files\System\ado\msado15.dll

Sub TestCustom()
    
    Const sFileName As String = "C:\Users\Simon\source\repos\Windows-class-samples\Samples\Win7Samples\dataaccess\osp\customer.txt"
    Debug.Assert Dir(sFileName) <> ""

    Dim oConn As ADODB.Connection
    Set oConn = New ADODB.Connection
    
    '*
    '* ospsampc is progid of the custom simple provider
    '*
    oConn.Open "Provider=MSDAOSP;Data Source=ospsampc"
    
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    
    Set rs.ActiveConnection = oConn
    
    rs.Open sFileName
    rs.MoveFirst
        
    '* lets get the recordset to a variant array
    Dim vVarArray
    vVarArray = rs.GetRows
End Sub

Tweak to given sample code

So the sample code is at Windows-classic-samples/Samples/Win7Samples/dataaccess/osp/vc at master · Microsoft/Windows-classic-samples · GitHub . It compiles fine for me no problem. One slight tweak is that the output filename needs to be changed. So here are instructions...

1. Right-click on project icon and from menu select Properties
2. In Property Pages, select General in the left hand pane
3. On the right hand pane, change Target Name from $(ProjectName) to ospsampc

Call Graph Walkthrough

The choreography between the classes can be a little confusing so I carefully stepped through the code and annotated it as this is the best way to learn the interaction. What you see below is VBA lines of code interleaved with the C++ sample code that gets called (typically via the OLEDB Simple Provider Dll, msdaosp.dll). Most of it is COM QueryInterface and COM initialisation. I have omitted calls to AddRef, Release and and calls to listening mechanisms as they cluttered the graph.

I recommend reading through the call graph but if you want a summary here it is VBA passes in the conn

1. VBA passes ADO a connection string to the Connection.Open method, Provider=MSDAOSP;Data Source=ospsampc
2. ADO sees the Provider=MSDAOSP and knows to load the OLEDB Simple Provider and passes control to its Dll, msdaosp.dll
3. msdaosp.dll parses the second term of the connection string to find the custom implementation's progid, Data Source=ospsampc
4. The progid ospsampc is found in the registry and the sample dll, ospsampc.dll, is loaded.
5. ospsampc.dll is called via entry point DllGetClassObject to get an object that implements IClassFactory.
6. IClassFactory.CreateInstance is called and ospsampc.dll returns an instance of MyDataSource (whilst creating an instance of MyOSPObject for later).
7. The instance of MyDataSource is queried for interface IDataSource
8. Control is passed back to VBA
9. VBA code creates recordset and sets active connection. No sample code is called for this (must be purely ADO and/or msdaosp.dll).

10. VBA code passes a filepath as the string parameter to the ADO recordset's Open method.
11. IDataSource::getDataMember is called with the filepath passed in to bstrDM parameter, this method is implemented by MyDataSource::getDataMember.
12. MyDataSource::getDataMember calls MyOSPObject::Init on its private instance of MyOSPObject passing the filepath as parameter.
13. MyOSPObject::Init loads the file into memory
14. MyDataSource::getDataMember queries MyOSPObject for the OLEDBSimpleProvider interface
15. The OLEDB Simple provider dll, msdaosp.dll can now call MyOSPObject directly on its OLEDBSimpleProvider interface implementation
16. msdaosp.dll calls MyOSPObject's OLEDBSimpleProvider interface implementation for row and column count etc as part of initialisation
17. Control is passed back to VBA
18. From now on, when VBA calls ADO to manipulate recordset msdaosp.dll makes calls on MyOSPObject's OLEDBSimpleProvider interface implementation.

VBA:    oConn.Open "Provider=MSDAOSP;Data Source=ospsampc"
C:\Program Files (x86)\Common Files\system\ole db\msdaosp.dll

 //
 // get the class factory by calling DllGetClassObject
 //
  ospsampc.dll!DllGetClassObject(const _GUID & rclsid, const _GUID & riid, void * * ppvObj) 
   ospsampc.dll!MyClassFactory::MyClassFactory() 
   ospsampc.dll!MyClassFactory::QueryInterface(const _GUID & riid, void * * ppv) 

 //
 // call CreateInstance on class factory, creates a MyDataSource (and during initialisation internally creates a MyOSPObject)
 // returns an IUnknown , no QI for IDataSource yet
 //
  ospsampc.dll!MyClassFactory::CreateInstance(IUnknown * pUnkOuter, const _GUID & riid, void * * ppv) 
   ospsampc.dll!MyDataSource::MyDataSource() 
  ospsampc.dll!MyDataSource::Init() 
    ospsampc.dll!MyOSPObject::MyOSPObject() 
    ospsampc.dll!CExList::CExList() 
  ospsampc.dll!MyDataSource::QueryInterface(const _GUID & riid, void * * ppv) 


 //
 // After some interim calls to QI for IUnknown we soon get a QI for IDataSource
 //
 ospsampc.dll!MyDataSource::QueryInterface(const _GUID & riid, void * * ppv) 

VBA:    Set rs = New ADODB.Recordset
VBA:    Set rs.ActiveConnection = oConn
VBA:    rs.Open sFileName
C:\Program Files (x86)\Common Files\system\ole db\msdaosp.dll

 //
 // the parameter passed by VBA in the rs.Open method is passed as bstrDM below
 // in this case a file path which gthis implementation will load and process
 // but the string could be an a block of COM moniker pointing to a block of cells on an Excel worksheet
 //
 // after initialization MyOSPObject is QI'ed for OLEDBSimpleProvider interface
 //
 ospsampc.dll!MyDataSource::getDataMember(wchar_t * bstrDM, const _GUID & riid, IUnknown * * ppUnk) 
  ospsampc.dll!MyOSPObject::Init(wchar_t * pwszFilePath) 
  ospsampc.dll!MyOSPObject::QueryInterface(const _GUID & riid, void * * ppv) 


C:\Program Files (x86)\Common Files\system\ole db\msdaosp.dll
 //
 // now msdaosp.dll calls MyOSPObject directly to QI for OLEDBSimpleProvider (again)
 // and then starts calling on OLEDBSimpleProvider::getRowCount , OLEDBSimpleProvider::getColumnCount   etc.
 //
 ospsampc.dll!MyOSPObject::QueryInterface(const _GUID & riid, void * * ppv) Line 87 
 ospsampc.dll!MyOSPObject::getRowCount(long * pcRows) 
 ospsampc.dll!MyOSPObject::getColumnCount(long * pcColumns) 
  ospsampc.dll!MyOSPObject::isAsync(int * pbAsynch)

Links

• Windows-classic-samples/Samples/Win7Samples/dataaccess/osp/vc at master · Microsoft/Windows-classic-samples · GitHub
• VS6Disk2

Appendix A - Registry Entries for sample

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\WOW6432Node\CLSID\{1E79B2C1-077B-11d1-B3AE-00AA00C1A924}]
@="ospsampc"

[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\WOW6432Node\CLSID\{1E79B2C1-077B-11d1-B3AE-00AA00C1A924}\InprocServer32]
"ThreadingModel"="Both"
@="C:\\Users\\Simon\\source\\repos\\Windows-class-samples\\Samples\\Win7Samples\\dataaccess\\osp\\vc\\Debug\\ospsampc.dll"

[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\WOW6432Node\CLSID\{1E79B2C1-077B-11d1-B3AE-00AA00C1A924}\ProgID]
@="ospsampc"

[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\WOW6432Node\CLSID\{1E79B2C1-077B-11d1-B3AE-00AA00C1A924}\VersionIndependentProgID]
@="ospsampc"

Appendix B - Sample database file

So the given database file has semicolons separating fields with newline as row terminator. There is also a row and column count on the top row. Also we have a row of column headers. No schema information though. Here at the top rows...

90;10
CustomerID;CompanyName;ContactName;ContactTitle;Address;City;Region;PostalCode;Country;Phone;
ALFKI;Alfreds Futterkiste;Maria Anders;Sales Representative;Obere Str. 57;Berlin;;12209;Germany;030-0074321;
ANATR;Ana Trujillo Emparedados y helados;Ana Trujillo;Owner;Avda. de la Constitución 2222;México D.F.;;05021;Mexico;(5) 555-4729;
ANTON;Antonio Moreno Taquería;Antonio Moreno;Owner;Mataderos  2312;México D.F.;;05023;Mexico;(5) 555-3932;
AROUT;Around the Horn;Thomas Hardy;Sales Representative;120 Hanover Sq.;London;;WA1 1DP;UK;(71) 555-7788;
BERGS;Berglunds snabbköp;Christina Berglund;Order Administrator;Berguvsvägen  8;Luleå;;S-958 22;Sweden;0921-12 34 65;
BLAUS;Blauer See Delikatessen;Hanna Moos;Sales Representative;Forsterstr. 57;Mannheim;;68306;Germany;0621-08460;

OLEDB - C++ - Solved: ATL's OLEDB Provider sample crashes Excel (uncaught exception from msado15.dll)

Happy Conclusion

This post initially did not reach a happy conclusion, I investigated compiling the ATL OLEDB Provider samples but reached an impasse as the ADO runtime throws an exception. I wrote up the problem as a blog post nevertheless because sometimes defeats can be as revealing as victories. I asked on StackOverflow and they came through. Especially Simon Mourier on StackOverflow solved it and I am thankful. I have not changed the main body of the original post, instead I have posted solution at the bottom here.

ADO Recordsets as a marshalling vessel

So I was pondering how to get data from Python to VBA without using COM's SAFEARRAY which can often but not always be acquired with tolist(). One alternative is to create an ADO recordset, it is possible to create an ADO recordset by creating the an Xml representation, i.e. to concatenate a correctly formatted string.

In the era of Visual Basic 5 (upon which VBA is based) code marshalled tabular data from one execution process to another (perhaps even on a different computer as part of an N-tiered scalable architecture, DNA) by transmitting a two dimensional array (SafeArray) housed in OLE Variant. In VB6, the recommended vessel for marshalling tabular became the disconnected ADO recordset. Marshalling means serializing in one process and then deserializing in another. For recordsets, the ADO run-time would do the marshalling where as for OLE Variants it was the COM run-time. They have similar speeds but an ADO recordset packs so much more functionality that one should opt for it.

ADO Recordsets come from OLE DB Providers

The vast majority of ADO recordsets are created by an OLE DB Provider such as the Microsoft SQL Server OLE DB Provider. Can we create our own OLE DB Provider? In theory yes, ATL has had for almost two decades the ability to create OLE DB Providers. I never tried until now. My attempt to get the sample working have got stuck. I'll try not to replicate the documentation, instead I will give links plus some extra info where pertinent.

The ATL Sample OLEDB Provider that Finds Files from a directory

ADO and OLEDB were meant to be an advance on the previous generation of data access technology by allowing a single object model to access not just database tables but also non-tabular data sources such as email stores.

In the case of the ATL Sample OLEDB Provider I have read enough code to say that it (when if works, if ever) actually creates a recordset where each row is a file in a given directory. This is useful as a sample because everyone has a file system and this obviates the need to install a database. It is also illustrative of a non-database source.

The sample reads files by calling API functions such as FindFirstFile.

When running the ATL OLEDB Provider wizard one can customise the name, I will choose "FindFiles".

Running Visual Studio 2017 ATL Wizards to create Find File OLEDB Provider Sample

I will try not to recreate the documentation but will give some screenshots as the official documentation does not (I suspect it is not being maintained).

Because we are creating a COM component you will need to run Visual Studio with admin rights

New Project -> Visual C++ -> ATL -> ATL Project

Click OK on the next screen without entering anything

In the solution explorer you'll find two projects, focus on the top project, FindFiles. On the FindFiles project icon or or any folder icon in the FindFiles project right-click mouse button and then select Add -> New Item . You'll get the following screen from which you should select ATL -> ATL OLEDB Provider and then click Add

The next screen is the wizard, enter FindFiles into the ShortName and (nearly) all the other fields are updated to reflect the short name. Also, add FindFiles to the ProgID, whilst not strictly necessary this will help debugging later. Click finish to commit and the wizard will write the code for you.

Save all the files and compile (don't forget you'll need admin rights).

Enumerating OLE DB Providers

I forget where I found this code (somewhere on Microsoft.com) but it enumerates all the OLE DB Providers installed, after a successful compilation FindFiles should appear

using System;
using System.Data;
using System.Data.OleDb;

namespace OldDbEnumerator
{

    class Program
    {
        static void Main()
        {
            OleDbDataReader reader = OleDbEnumerator.GetRootEnumerator();

            DisplayData(reader);

            Console.WriteLine("Press any key to continue.");
            Console.ReadKey();
        }

        static void DisplayData(OleDbDataReader reader)
        {
            while (reader.Read())
            {
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    Console.WriteLine("{0} = {1}",
                     reader.GetName(i), reader.GetValue(i));
                }
                Console.WriteLine("==================================");
            }
        }
    }
}

And in the output of that is the new Provider, your GUID will be different (it is randomly selected)

SOURCES_NAME = FindFiles Class
SOURCES_PARSENAME = {E387836C-6248-4319-92E8-BCD070844D86}
SOURCES_DESCRIPTION = FindFiles Class
SOURCES_TYPE = 1
SOURCES_ISPARENT = False
SOURCES_CLSID = {E387836C-6248-4319-92E8-BCD070844D86}

Excel VBA client code CRASHES EXCEL

Sorry for caps shouting but it is important to save your Excel session before you play with the next chunk of code. Here is some client VBA code. It crashes Excel VBA.

We'll use for debugging later so give your self a new workbook called TestClient.xlsm, in a new standard module paste the following code

Sub TestOleDbProvider()
    
    On Error GoTo ErrHand
    
    
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    
    cn.Open "Provider=FindFiles;Server=foo;Database=bar" '* this works
    
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    
    Set cmd.ActiveConnection = cn   '* this works
    
    cmd.CommandText = "*.*"   '* this works
    
    Stop
    Dim rs As ADODB.Recordset
    Set rs = cmd.Execute  '* crashes here
    
    
    
    Exit Sub
ErrHand:
    Debug.Print Err.Description & " (" & Err.Number & ")"
    'Stop

End Sub

Before you run this code, goto ThisWorkbook and enter the following helpful code which will always navigate to the above code upon workbook opening...

Private Sub Workbook_Open()
    Application.GoTo "TestOleDbProvider"
End Sub

Then save the workbook before you run TestOleDbProvider because it will crash and you will not have an opportunity to save it!

So running the above code crashes Excel, later I will show that an uncaught exception is being thrown by msado15.dll. Now this maybe to do with the fact I do not know how to yet supply a query. Peaking ahead I can show some of the C++ code (CFindFilesRowset::Execute) where it is assumes *.* if supplied an empty string

  CW2TEX<_MAX_PATH> szDir(m_strCommandText == L"" ? L"*.*" : m_strCommandText);

Time to investigate.

Debugging the OLEDB Provider with breakpoints

So we must investigate. I set the project's properties to start Excel and load a workbook called TestClient.xlsm as part of the debug properties. So on the FileFind project icon right-click and select properties (last entry on the menu) to display FindFiles Property Pages within which select the Debugging entry on the left hand side.

Running the code I get an unhandled exception and the call stack is firmly in msado15.dll..

 msado15.dll!CQuery::SetSQL(unsigned short *) Unknown Non-user code. Symbols loaded.
  msado15.dll!CQuery::SetCommandText(long,unsigned long,unsigned char,unsigned char) Unknown Non-user code. Symbols loaded.
  msado15.dll!CQuery::Execute(enum ExecuteTypeEnum,char,unsigned long,bool,unsigned long,unsigned long,long,struct tagVARIANT *,unsigned long,void *,long *,struct _ADORecordset * *) Unknown Non-user code. Symbols loaded.
  msado15.dll!CCommand::_Execute(enum ExecuteTypeEnum,char,unsigned long,bool,unsigned long,unsigned long,long,long,struct tagVARIANT *,unsigned long,void *,long *,struct _ADORecordset * *) Unknown Non-user code. Symbols loaded.
  msado15.dll!CCommand::ExecuteWithModeFlag(struct tagVARIANT *,struct tagVARIANT *,long,struct _ADORecordset * *,int) Unknown Non-user code. Symbols loaded.
  msado15.dll!CCommand::Execute(struct tagVARIANT *,struct tagVARIANT *,long,struct _ADORecordset * *) Unknown Non-user code. Symbols loaded.
  VBE7.DLL!1e813579() Unknown No symbols loaded.
  [Frames below may be incorrect and/or missing, no symbols loaded for VBE7.DLL]  Annotated Frame
  VBE7.DLL!1e7cff4b() Unknown No symbols loaded.
  VBE7.DLL!1e829d13() Unknown No symbols loaded.
  VBE7.DLL!1e82fea2() Unknown No symbols loaded.
  VBE7.DLL!1e82bcb5() Unknown No symbols loaded.
  [External Code]  Annotated Frame

And with that I am stuck. I guess I could ask Stack Overflow. I've never bothered Microsoft for support before, might do so on this occasion. I'll try a SO bounty first I think.

Update: Solution given by Simon Mourier

I am delighted to say that Simon Mourier at StackOverflow solved this by adding another interface ICommandText to the interface map.

BEGIN_COM_MAP(CFindFilesCommand)
    ...
    COM_INTERFACE_ENTRY(ICommandText) 
    ...
END_COM_MAP()

Apparently, the ADO runtime was querying for this interface and not finding it and thus calling on a null pointer. I'm surprised the ADO runtime doesn't check for null pointers but never mind. The cased is solved.

Python - Pandas - ADO - Convert a Pandas DataFrame to an ADO Recordset

So it's Python month on this Excel Development Platform blog where I highlight some Python technologies of interest to Excel (VBA) Developers.

So Python has the Pandas data processing library and one could move logic from VBA into a Python middle tier application server but sometimes you may still want some data processing functionality to remain in the VBA layer. Can a Pandas DataFrame be converted to an ADO Recordset? Yes, but you'll need to convert it into an Xml representation first and then pass the string to VBA where it recreates the ADO recordset.

Python code to convert Pandas dataframe to Xml representation of an ADO Recordset

Much of the Xml representation of an ADO recordset is boilerplate code, however in the first section one can see the column names of Col1,Col2,Col3. Then in the z:row elements the field values are added as attributes and the attribute names must match the column names Col1,Col2,Col3. Then add a tail and return the whole string to VBA.

import pandas as pd
import numpy as np

class PopulationDensity(object):
    _reg_clsid_ = "{C50910CC-F88F-4EA5-86D4-1E5D6AF1F4AE}"
    _reg_progid_= 'PandasInVBA.PopulationDensity'
    _public_methods_ = ['getPivotTable','getADORecordset']

    def getADORecordset(self):
        url="https://raw.githubusercontent.com/datasets/house-prices-uk/master/data/data.csv"
        whole=pd.read_csv(url)

        ## project first three columns
        projected = whole[['Date','Price (All)','Change (All)']]

        ## So now start creating the Xml representation of an ADO Recordset
        sStart = ("<xml xmlns:x='urn:schemas-microsoft-com:office:excel' "  +
        "    xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882' "  +
        "    xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882' "  +
        "    xmlns:rs='urn:schemas-microsoft-com:rowset' "  +
        "    xmlns:z='#RowsetSchema'>"  +
        "<x:PivotCache>"  +
        "<x:CacheIndex>1</x:CacheIndex>"  +
        "<s:Schema id='RowsetSchema'>"  +
        "<s:ElementType name='row' content='eltOnly'>"  +
        "<s:attribute type='Col1'/>"  +
        "<s:attribute type='Col2'/>"  +
        "<s:attribute type='Col3'/>"  +
        "<s:extends type='rs:rowbase'/>"  +
        "</s:ElementType>"  +
        "<s:AttributeType name='Col1' rs:name='Date'>"  +
        "<s:datatype dt:maxLength='255'/>"  +
        "</s:AttributeType>"  +
        "<s:AttributeType name='Col2' rs:name='Price (All)'>"  +
        "<s:datatype dt:maxLength='255'/>"  +
        "</s:AttributeType>"  +
        "<s:AttributeType name='Col3' rs:name='Change (All)'>"  +
        "<s:datatype dt:maxLength='255'/>"  +
        "</s:AttributeType>"  +
        "</s:Schema>"  +
        "<rs:data>" )

        ## now the data section, we iterate over the rows of the pandas DataFrame
        sData =""
        for index, row in projected.iterrows():
            sData = sData + "<z:row Col1='" + str(row['Date']) + "' Col2='" + str(row['Price (All)']) + "' Col3='" + str(row['Change (All)']) + "'/>"

        sEnd = (
        "</rs:data>"  +
        "</x:PivotCache>"  +
        "</xml>"  )

        return sStart + sData + sEnd 

    def getPivotTable(self):
        pass  # see previous article

if __name__=='__main__':
    print ("Registering COM server...")
    import win32com.server.register
    win32com.server.register.UseCommandLine(PopulationDensity)

Client VBA Code

So here is the VBA code. An Xml Dom document is created, and the string returned from Python is parsed as a document. Then an ADO recordset is created and we call Open passing the DomDocument as the argument. This technique leverages the fact that recordsets can be persisted to xml files.

Sub Test2()

    Dim obj As Object
    Set obj = VBA.CreateObject("PandasInVBA.PopulationDensity")
    
    Dim sDataAsXml As String
    sDataAsXml = obj.getADORecordset
    
    '* Tools->References:Microsoft Xml, v6.0
    Dim domXlPersist As MSXML2.DOMDocument60
    Set domXlPersist = New MSXML2.DOMDocument60
    domXlPersist.LoadXML sDataAsXml
    Debug.Assert domXlPersist.parseError.ErrorCode = 0
    
    '* Tools->References:Microsoft ActiveX Data Object 6.1 Library
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.Open domXlPersist
    

    Dim rngOrigin As Excel.Range
    Set rngOrigin = Sheet3.Cells(12, 1)
    
    '* write column headers
    Dim lFieldLoop As Long
    For lFieldLoop = 0 To rs.Fields.Count - 1
        rngOrigin.Offset(0, lFieldLoop).Value = rs.Fields.Item(lFieldLoop).Name
    Next lFieldLoop
    
    '* write the data, yes, in one line
    rngOrigin.Offset(1, 0).CopyFromRecordset rs

End Sub

Final Thoughts

In the code given I have serialized a Dataframe to a (potentially large) Xml string then on the client side parsed it into a Dom and then an ADO recordset. This is quite a heavy set of operations. If you are calling an in process component then it would be better to pass it back as an OLE Variant. However, the above technique maybe better suited for Flask web services where conversion to strings is standard practice as part of the HTTP protocol.

ActiveDirectory with VBA Part 5 - Querying LDAP with ADO

So in addition to using the GetObject("LDAP//... syntax one can also query LDAP Active Directory using ActiveX Data Objects (ADO). You will need to use the Microsoft OLE DB Provider for Microsoft Active Directory Service.

I can give some VBA which queries the AD LDS instance created in part 1, part 2 and part 4 of this series. If you run the following code you should get the table of results shown below.

Sub QueryingLDAPWithADO()
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=ADSDSOObject" '* ref https://msdn.microsoft.com/en-us/library/aa746471(v=vs.85).aspx
    cn.Open

    Sheet1.Cells.ClearContents
    Sheet1.Range("A1:F1").Font.Italic = False
    Sheet1.Range("A1:F1").Font.Bold = True
    Sheet1.Range("A2:F2").Font.Italic = True


    Dim cmdSQL_Dialect As ADODB.Command
    Set cmdSQL_Dialect = New ADODB.Command
    cmdSQL_Dialect.CommandText = "Select nAME,distinguishedName FROM 'LDAP://localhost:389/o=Microsoft,c=US' WHERE objectClass='*'"

    Set cmdSQL_Dialect.ActiveConnection = cn
    
    Dim rsSQL_Dialect As ADODB.Recordset
    Set rsSQL_Dialect = cmdSQL_Dialect.Execute
    Sheet1.Cells(1, 1).Value = "SQL Dialect query"
    Sheet1.Cells(2, 2).Value = "Name"    '* seems the fields are in reversed order, perhaps alphabetical?
    Sheet1.Cells(2, 1).Value = "DistinguishedName"
    Sheet1.Cells(3, 1).CopyFromRecordset rsSQL_Dialect

    Dim cmdLDAP_Dialect As ADODB.Command
    Set cmdLDAP_Dialect = New ADODB.Command
    cmdLDAP_Dialect.CommandText = ";(objectClass=*);CN; subtree"
    
    Set cmdLDAP_Dialect.ActiveConnection = cn
    
    Dim rsLDAP_Dialect As ADODB.Recordset
    Set rsLDAP_Dialect = cmdLDAP_Dialect.Execute
    
    Sheet1.Cells(1, 4).Value = "LDAP Dialect query"
    Sheet1.Cells(2, 4).Value = "CN"
    Sheet1.Cells(3, 4).CopyFromRecordset rsLDAP_Dialect
End Sub

Excel on the Server? No thanks, Xml ADO recordsets please

I have encountered a variety of what I would call "Excel on the server" technologies and these include Microsoft SharePoint Server but also there is an Apache (and thus open source) Java Apache-POI, I chanced upon the latter whilst looking at StackOverflow bounties. Mulling the use case of generating excel workbooks on a server I think that the majority use case is the creation of reports, and the best way to do this is pivot tables and charts based on those pivot table. But is the creation of pivot tables in an Excel workbook on a server a smart thing to do? If you look at some sample Apachi-POI code it would appear a bit clunky.

In this older post I show worksheet cell contents converted to Xml and then to an ActiveX Data Objects (hereafter ADO) recordset. Use of ADO recordsets as a means to marshalling data between a client desktop and a computer room server should not be underestimated. Indeed, in the era of Visual Basic 6 the N-tier architecture was Windows DNA and all these distributed architectures require some state container/vessel to marshal data from one tier to another. For Windows DNA an ADO recordset that the state marshalling container/vessel.

So I would recommend web services emitting a Xml version of an ADO recordset to an Excel workbook. The magic line of code that eliminates a ton of scripting is the CopyFromRecordset method, it is the penultimate line in the following VBA example. You'll need the Xml to be saved into a file (I have chosen c:\temp\xl_persists_2.xml)

<xml xmlns:x="urn:schemas-microsoft-com:office:excel" 
    xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" 
    xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" 
    xmlns:rs="urn:schemas-microsoft-com:rowset" 
    xmlns:z="#RowsetSchema">
<x:PivotCache>
<x:CacheIndex>1</x:CacheIndex>
<s:Schema id="RowsetSchema">
<s:ElementType name="row" content="eltOnly">
<s:attribute type="Col1"/>
<s:attribute type="Col2"/>
<s:attribute type="Col3"/>
<s:extends type="rs:rowbase"/>
</s:ElementType>
<s:AttributeType name="Col1" rs:name="FirstName">
<s:datatype dt:maxLength="255"/>
</s:AttributeType>
<s:AttributeType name="Col2" rs:name="FamilyName">
<s:datatype dt:maxLength="255"/>
</s:AttributeType>
<s:AttributeType name="Col3" rs:name="Role">
<s:datatype dt:maxLength="255"/>
</s:AttributeType>
</s:Schema>
<rs:data>
<z:row Col1="John" Col2="Snow" Col3="President"/>
<z:row Col1="Ygritte" Col2="Wild" Col3="Vice-President"/>
</rs:data>
</x:PivotCache>
</xml>

For the VBA you'll need Tools->References to Microsoft ActiveX Data Object 6.1 Library (or similar) and Microsoft Xml, v6.0 (or similar)

Function RecordsetAsXml() As String
    '* in this example I'm loading from a file but it can be a webservice.
    
    RecordsetAsXml = VBA.CreateObject("Scripting.FileSystemObject").OpenTextFile("c:\temp\xl_persist_2.xml").ReadAll
End Function

Sub LoadXmlRecordset()

    'Tools->References:Microsoft ActiveX Data Object 6.1 Library
    Dim rs As ADODB.Recordset
    
    'Tools->References:Microsoft Xml, v6.0
    Dim domRecordsetAsXml As MSXML2.DOMDocument60
    Set domRecordsetAsXml = New MSXML2.DOMDocument60
    domRecordsetAsXml.LoadXML RecordsetAsXml
    Debug.Assert domRecordsetAsXml.parseError.ErrorCode = 0

    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.Open domRecordsetAsXml
    
    '* placed a little under the original data for comparison
    Dim rngOrigin As Excel.Range
    Set rngOrigin = ThisWorkbook.Worksheets.Item(1).Cells(6, 1)
    
    Dim lFieldLoop As Long
    For lFieldLoop = 0 To rs.Fields.Count - 1
        rngOrigin.Offset(0, lFieldLoop).Value = rs.Fields(lFieldLoop).Name
    Next lFieldLoop
    
    rngOrigin.Offset(1).CopyFromRecordset rs

End Sub

From this point it is very easy to generate a pivot table and charts from the table of data zapped into the worksheet by CopyFromRecordSet. So, I prefer Xml ADO recordsets to Sharepoint or Apache POI generated workbooks.

VBA - Populating ADO Recordset with contents from Worksheet (recognising column names)

Also from StackOverflow an interesting tip arose concerning the ability to populate an ADO recordset with the Xml persisted contents of a block of cells.  The sample code however did not work in that the recordset did not understand the column names, what was required was some manipulation of the Xml schema in between.  Here is some code

Option Explicit
Option Private Module

Const mlDATASHEET As Long = 1

Private Sub SetUpTestData_RunOnce()

    Dim vTestData As Variant
    vTestData = [{"FirstName","FamilyName","Role";"John","Snow","President";"Ygritte","Wild","Vice-President"}]
    
    Dim rng As Excel.Range
    Set rng = ThisWorkbook.Worksheets.Item(mlDATASHEET).Cells(1, 1).Resize(UBound(vTestData, 1), UBound(vTestData, 2))
    rng.Value = vTestData

End Sub

Private Sub Test_RunMany()
    
    Dim rng As Excel.Range
    Set rng = ThisWorkbook.Worksheets.Item(mlDATASHEET).Cells(1, 1).CurrentRegion
    Dim sDataAsXml As String
    sDataAsXml = rng.Value(xlRangeValueMSPersistXML)
    '*DEBUG VBA.CreateObject("Scripting.FileSystemObject").CreateTextFile("c:\temp\xl_persist.xml").Write sDataAsXml
    
    'Tools->References:Microsoft ActiveX Data Object 6.1 Library
    Dim rs As ADODB.Recordset
    
    'Tools->References:Microsoft Xml, v6.0
    Dim domXlPersist As MSXML2.DOMDocument60
    Set domXlPersist = New MSXML2.DOMDocument60
    domXlPersist.setProperty "SelectionLanguage", "XPath"
    domXlPersist.setProperty "SelectionNamespaces", "xmlns:x='urn:schemas-microsoft-com:office:excel'" & _
         " xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882' " & _
         " xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882' " & _
         " xmlns:rs='urn:schemas-microsoft-com:rowset' xmlns:z='#RowsetSchema'"
    domXlPersist.LoadXML sDataAsXml
    
    Dim attrTypeLoop As MSXML2.IXMLDOMNodeList
    Set attrTypeLoop = domXlPersist.SelectNodes("xml/x:PivotCache/s:Schema/s:AttributeType")
    
    Dim xmlFirstDataRow As MSXML2.IXMLDOMElement
    Set xmlFirstDataRow = domXlPersist.SelectSingleNode("xml/x:PivotCache/rs:data/z:row[1]")
    
    Dim attrColLoop As MSXML2.IXMLDOMAttribute, lLoop As Long
    For Each attrColLoop In xmlFirstDataRow.Attributes
        
        Dim attrType As MSXML2.IXMLDOMElement
        Set attrType = attrTypeLoop.Item(lLoop)
        
        Call attrType.setAttribute("rs:name", attrColLoop.Value)
        
        
        lLoop = lLoop + 1
    Next attrColLoop


    xmlFirstDataRow.ParentNode.RemoveChild xmlFirstDataRow
    Set xmlFirstDataRow = Nothing
    
    '*DEBUG  VBA.CreateObject("Scripting.FileSystemObject").CreateTextFile("c:\temp\xl_persist_2.xml").Write domXlPersist.XML
    
    'STOP
    
    
    Set rs = New ADODB.Recordset
    rs.Open domXlPersist

    
    'Stop


    Debug.Assert rs.Fields.Count = 3
    Debug.Assert rs.Fields.Item(0).Name = "FirstName"
    Debug.Assert rs.Fields.Item(1).Name = "FamilyName"
    Debug.Assert rs.Fields.Item(2).Name = "Role"
    
    Debug.Assert rs.RecordCount = 2
    
    rs.Filter = "FirstName='John'"
    
    Debug.Assert rs.RecordCount = 1
    
    Stop

End Sub

If you uncomment the debug line then you'll write an interim xml file, if you're interested in the contents here they are

<xml xmlns:x="urn:schemas-microsoft-com:office:excel" 
    xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" 
    xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" 
    xmlns:rs="urn:schemas-microsoft-com:rowset" 
    xmlns:z="#RowsetSchema">
<x:PivotCache>
<x:CacheIndex>1</x:CacheIndex>
<s:Schema id="RowsetSchema">
<s:ElementType name="row" content="eltOnly">
<s:attribute type="Col1"/>
<s:attribute type="Col2"/>
<s:attribute type="Col3"/>
<s:extends type="rs:rowbase"/>
</s:ElementType>
<s:AttributeType name="Col1" rs:name="FirstName">
<s:datatype dt:maxLength="255"/>
</s:AttributeType>
<s:AttributeType name="Col2" rs:name="FamilyName">
<s:datatype dt:maxLength="255"/>
</s:AttributeType>
<s:AttributeType name="Col3" rs:name="Role">
<s:datatype dt:maxLength="255"/>
</s:AttributeType>
</s:Schema>
<rs:data>
<z:row Col1="John" Col2="Snow" Col3="President"/>
<z:row Col1="Ygritte" Col2="Wild" Col3="Vice-President"/>
</rs:data>
</x:PivotCache>
</xml>

So actually this is some useful code for rustling up a sample recordset.