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.
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.
