Excel column datatype issues using MSADO and Access 2016 Engine driver

Question

I'm having an issue updating empty cells in an Excel spreadsheet using the Microsoft Access 2016 OLE DB driver, which I invoke from MSADO/C++. Updating the field causes a "Type Mismatch" exception.

My understanding of the issue was that the Access OLE DB driver determines the datatype of a column by scanning a number of rows as per the TypeGuessRows setting in the registry at Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel

I've tried different values for this setting, and none made a difference. It seems the driver is scanning all the column's cells, and typing them as VT_NULL, rather than VT_BSTR. If the column is already populated with values, the problem goes away.

The spreadsheet, which is very simple, looks like this:

What I'm trying to do is update the "Target" column with translations of the cells in "Source". I create an ADO Recordset as follows:

CString m_szSQLStatement = L"SELECT [Sheet1$].[ID], [Sheet1$].[Source], [Sheet1$].[Target] FROM [Sheet1$]";
m_pRecordSet->CursorLocation = adUseServer;
m_pRecordSet->Open((_bstr_t) m_szSQLStatement.GetBuffer(0),_bstr_t( m_szConnectionStr.GetBuffer(0) ),adOpenKeyset,adLockOptimistic,
   adCmdText);
_bstr_t bstrFieldName = _bstr_t(L"Target");
FieldPtr pField = m_pRecordSet->Fields->GetItem(bstrFieldName);
pField->Value = "new value"

I've tried a few different things to workaround this issue:

• Calling pField->Value.ChangeType(VT_BSTR)

• Initializing the column value with an empty _bstr_t like this: pFields->GetItem(bstrFieldName)->Value = (_bstr_t)L""

• Explicitly casting the column to a string within my SQL query using the CStr() function provided by MS-Access.

• Modifying the schema of the document before retrieving data from it using ALTER TABLE ALTER COLUMN "Target" VARCHAR(65535) - this returned an error saying "operation not supported"

• Adding IMEX=1 to my connection string's Extended Properties field - this rendered the spreadsheet read only

• Setting TypeGuessRows to 0 in the registry as mentioned previously.

None of the above worked, and I'm now out of ideas. I'm not sure if it's even possible to do what I want with my spreadsheet, but was hoping someone with more knowledge could point me in the right direction.

Thanks