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.
