So finally, rewritting the code into ADO helped.
Here is the ADO coding.
Firstly, Access DB is created. There is one trouble with this - simple ADO cannot do this, therefore, you have to use additional extension component ADOX (Microsoft ADO Ext. x.x for DDL and Security) - more details (
here)
Dim lADOConnection As ADODB.Connection = Nothing
Dim lCatalog As New ADOX.Catalog()
Dim lObject As Object = lCatalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & PivotFilePath)
lADOConnection = DirectCast(lObject, ADODB.Connection)
Dim lStrBuilder As New System.Text.StringBuilder()
For Each nFieldName As String In aFieldNames
If lStrBuilder.Length > 0 Then
lStrBuilder.Append(", ")
Else
lStrBuilder.Append("(")
End If
lStrBuilder.Append(nFieldName & " TEXT")
Next
lStrBuilder.Append(", " & PIVOT_VALUE_FIELD_NAME & " Double" & ")")
lStrBuilder.Insert(0, "CREATE TABLE " & PIVOT_TABLE_NAME & " ")
lADOConnection.Execute(lStrBuilder.ToString())
Then create recordset and fill it with data:
Dim lADORecordSet As New ADODB.Recordset()
lADORecordSet.CursorLocation = ADODB.CursorLocationEnum.adUseClient
lADORecordSet.CursorType = ADODB.CursorTypeEnum.adOpenDynamic
lADORecordSet.LockType = ADODB.LockTypeEnum.adLockOptimistic
lADORecordSet.ActiveConnection = lADOConnection
lADORecordSet.Open(CDatabaseUtils.PIVOT_TABLE_NAME, lADOConnection)
...
For nIndex As Integer = 0 To lLines.GetLength(0) - 1
If Not lValuesDBL(nIndex) Is Nothing Then
lADORecordSet.AddNew()
lADORecordSet.Fields(CDatabaseUtils.PIVOT_VALUE_FIELD_NAME).Value = lValuesDBL(nIndex)
For nIndex2 As Integer = 0 To lXlength + lHlength - 1
lADORecordSet.Fields(nIndex2).Value = lLines(nIndex, nIndex2)
Next
lADORecordSet.Update()
lADORecordSet.MoveNext()
End If
Next
...
lADORecordSet.Close()
lADOConnection.Close()
And finally, I use this coding to create the pivot table:
Dim lADOConnection As New ADODB.Connection()
Dim lADORecordSet As New ADODB.Recordset()
lADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CDatabaseUtils.PivotFilePath)
lADORecordSet.CursorLocation = ADODB.CursorLocationEnum.adUseClient
lADORecordSet.CursorType = ADODB.CursorTypeEnum.adOpenStatic
lADORecordSet.LockType = ADODB.LockTypeEnum.adLockOptimistic
lADORecordSet.Open("SELECT * FROM " & CDatabaseUtils.PIVOT_TABLE_NAME, lADOConnection)
lADORecordSet.ActiveConnection = Nothing
Dim lPivotCache As PivotCache = Globals.ThisWorkbook.PivotCaches.Create(XlPivotTableSourceType.xlExternal)
lPivotCache.Recordset = lADORecordSet
lPivotTable = lPivotCache.CreatePivotTable(TableDestination:=m_ActiveCell.Offset(15, 0),
TableName:=m_OptionsData.PivotName)
This works perfectly. But if the Access DB is created via DAO with ODBC connection, then the database is somehow corrupted and it is not possible to create the pivot table (there is error "Unrecognized database format").
The DAO coding to create the Access DB is following:
Dim lDatase As Database
Dim lDBEngine As New DBEngine()
lDatase = lDBEngine.CreateDatabase(PivotFilePath, LanguageConstants.dbLangGeneral)
Dim lTableDef As TableDef = lDatase.CreateTableDef(PIVOT_TABLE_NAME)
For Each nFieldName As String In aFieldNames
lTableDef.Fields.Append(lTableDef.CreateField(nFieldName, DataTypeEnum.dbText))
Next
lTableDef.Fields.Append(lTableDef.CreateField(PIVOT_VALUE_FIELD_NAME, DataTypeEnum.dbDouble))
lDatase.TableDefs.Append(lTableDef)
lDatabase.Close()
I have no idea why this does not work. If you have any hint, I would appreciate it.
I would rather use the DAO than the ADO. The reason is that to create the database with ADO, I have to use also the additional extension COM library "Microsoft ADO Ext. 6.0 for DLL and Security". I would like to avoid it and use native .NET only in the solution.