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

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 Simple Provider - Xml Reader

So I have chanced upon something call the OLEDB Simple Provider which allows VBA to convert an Xml document into a recordset. Worth investigating some more in its own right because it is said to support shaped, i.e. hierarchical recordsets. But right now, I want to find how to implement a custom OLEDB Simple Provider for an in memory array, a goal I've had for some time.

It has to be said that a recordset can be saved to Xml and indeed a saved Xml recordset can be synthesised as I did in this blog post.

Option Explicit

'* Tools->References
'MSXML2     Microsoft XML, v6.0                         C:\Windows\SysWOW64\msxml6.dll
'ADODB      Microsoft ActiveX Data Objects 6.1 Library  C:\Program Files (x86)\Common Files\System\ado\msado15.dll

Sub Test()
    Dim xmlDom As MSXML2.DOMDocument60
    Set xmlDom = New MSXML2.DOMDocument60
    
    Dim sColorsXml As String
    sColorsXml = "<colors>" & _
            "<color><colorname>Red</colorname><colorRGB>FF0000</colorRGB></color>" & _
            "<color><colorname>Green</colorname><colorRGB>00FF00</colorRGB></color>" & _
            "<color><colorname>Blue</colorname><colorRGB>0000FF</colorRGB></color></colors>"

    
    xmlDom.LoadXML sColorsXml
    Debug.Assert xmlDom.parseError = 0
    
    Const sFileName As String = "N:Colors.xml"
    xmlDom.Save sFileName

    Dim oConn As ADODB.Connection
    Set oConn = New ADODB.Connection
    'oConn.Open "Provider=MSDAOSP;Data Source=MSXML2.DSOControl.5.0"
    oConn.Open "Provider=MSDAOSP;Data Source=MSXML2.DSOControl"

    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    Set rs.ActiveConnection = oConn
    
    rs.Open sFileName
    rs.MoveFirst
    Debug.Assert rs.RecordCount = 3
    Debug.Assert rs.Fields.Count = 3  '* this surprised me
    Debug.Assert rs.Fields.Item(0).Name = "colorname"
    Debug.Assert rs.Fields.Item(1).Name = "colorRGB"
    Debug.Assert rs.Fields.Item(2).Name = "$Text"  '* this is the additional unexpected field, it concatentates the others
        
    '* lets get the recordset to a variant array
    Dim vVarArray
    vVarArray = Application.WorksheetFunction.Transpose(rs.GetRows)
    Debug.Assert vVarArray(1, 1) = "Red"
    Debug.Assert vVarArray(1, 2) = "FF0000"
    Debug.Assert vVarArray(1, 3) = "RedFF0000"
    Debug.Assert vVarArray(2, 1) = "Green"
    Debug.Assert vVarArray(2, 2) = "00FF00"
    Debug.Assert vVarArray(2, 3) = "Green00FF00"
    Debug.Assert vVarArray(3, 1) = "Blue"
    Debug.Assert vVarArray(3, 2) = "0000FF"
    Debug.Assert vVarArray(3, 3) = "Blue0000FF"
    
    Stop
End Sub

Appendix A - Registry Entries

Just doing some digging into the load sequence etc....

So "MSXML2.DSOControl" is a ProgId and we can go find it in the registry ...

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\Msxml2.DSOControl.5.0]
@="XML Data Source Object 5.0"

[HKEY_CLASSES_ROOT\Msxml2.DSOControl.5.0\CLSID]
@="{88D969E9-F192-11D4-A65F-0040963251E5}"

So we can lookup the CLSID, {88D969E9-F192-11D4-A65F-0040963251E5} ...

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{88D969E9-F192-11D4-A65F-0040963251E5}]
@="XML Data Source Object 5.0"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{88D969E9-F192-11D4-A65F-0040963251E5}\InProcServer32]
@="C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE11\MSXML5.DLL"
"ThreadingModel"="Apartment"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{88D969E9-F192-11D4-A65F-0040963251E5}\ProgID]
@="Msxml2.DSOControl.5.0"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{88D969E9-F192-11D4-A65F-0040963251E5}\TypeLib]
@="{F5078F18-C551-11D3-89B9-0000F81FE221}"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{88D969E9-F192-11D4-A65F-0040963251E5}\Version]
@="5.0"

We can find the type library with {F5078F18-C551-11D3-89B9-0000F81FE221} There were multiple versions, I'm showing version 5 ...

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\Wow6432Node\TypeLib\{F5078F18-C551-11D3-89B9-0000F81FE221}]

[HKEY_CLASSES_ROOT\Wow6432Node\TypeLib\{F5078F18-C551-11D3-89B9-0000F81FE221}\5.0]
@="Microsoft XML, v5.0"

[HKEY_CLASSES_ROOT\Wow6432Node\TypeLib\{F5078F18-C551-11D3-89B9-0000F81FE221}\5.0\0]
@=""

[HKEY_CLASSES_ROOT\Wow6432Node\TypeLib\{F5078F18-C551-11D3-89B9-0000F81FE221}\5.0\0\win32]
@="C:\\Program Files (x86)\\Common Files\\Microsoft Shared\\OFFICE11\\MSXML5.DLL"

[HKEY_CLASSES_ROOT\Wow6432Node\TypeLib\{F5078F18-C551-11D3-89B9-0000F81FE221}\5.0\FLAGS]
@="0"

[HKEY_CLASSES_ROOT\Wow6432Node\TypeLib\{F5078F18-C551-11D3-89B9-0000F81FE221}\5.0\HELPDIR]
@="C:\\Program Files (x86)\\Common Files\\Microsoft Shared\\OFFICE11\\"

Loading the type library C:\Program Files (x86)\Common Files\microsoft shared\OFFICE11.MSXML5.DLL to see what interfaces DSOControl supports we can see it only has one

[
  uuid(88D969E9-F192-11D4-A65F-0040963251E5),
  helpstring("XML Data Source Object")
]
coclass DSOControl50 {
    [default] interface IDSOControl;
};


[
  odl,
  uuid(310AFA62-0575-11D2-9CA9-0060B0EC3D39),
  helpstring("DSO Control"),
  hidden,
  dual,
  nonextensible,
  oleautomation
]
interface IDSOControl : IDispatch {
    [id(0x00010001), propget]
    HRESULT XMLDocument([out, retval] IXMLDOMDocument** ppDoc);
    [id(0x00010001), propput]
    HRESULT XMLDocument([in] IXMLDOMDocument* ppDoc);
    [id(0x00010002), propget]
    HRESULT JavaDSOCompatible([out, retval] long* fJavaDSOCompatible);
    [id(0x00010002), propput]
    HRESULT JavaDSOCompatible([in] long fJavaDSOCompatible);
    [id(0xfffffdf3), propget]
    HRESULT readyState([out, retval] long* state);
};

And there the trail goes cold, nothing interesting about that interface, certainly nothing that can help our custom implementation of OLEDB Simple Provider.