Click here to Skip to main content
15,877,675 members
Please Sign up or sign in to vote.
4.78/5 (4 votes)
Hello experts,

I am struggling with an issue on how to create pivot table with data taken from an external source - an existing MS Access database. The database can be opened in MS Access withou any problems and the data is available there.

I found 3 similar ways on how to implement it, but none of them works for me:

1. Creating recordset and add it to the PivotCache:
VB
Dim lPivotCache As PivotCache

Dim lDBEngine As New DBEngine()
Dim lDb As Database = lDBEngine.OpenDatabase(CDatabaseUtils.TempFilePath)
lRecordSet = lDb.OpenRecordset("SELECT * FROM " & CDatabaseUtils.SAP_PIVOT)
lPivotCache = Globals.ThisWorkbook.PivotCaches.Create(XlPivotTableSourceType.xlExternal)
lPivotCache.Recordset = lRecordSet
lPivotTable = lPivotCache.CreatePivotTable(TableDestination:=m_ActiveCell, TableName:=m_OptionsData.PivotName)

The connection to the database and the recordset is created correctly (I verified in debugger that the recordset really contains the data from the database file). But still an exception is raised on the line, where I try to assign the recordset to the pivotcache:
VB
lPivotCache.Recordset = lRecordSet

The exception is: {"Exception from HRESULT: 0x800A03EC"}

2. Use the PivotTableWizard method
VB
Dim lSql() As String = {"SELECT * FROM '" & CDatabaseUtils.TempFilePath & "'." & CDatabaseUtils.SAP_PIVOT}
Dim lConStr As String = "ODBC;DBQ=" & CDatabaseUtils.TempFilePath & ";DriverId=25;FIL=MS Access;"
m_ActiveSheet.PivotTableWizard(SourceType:=XlPivotTableSourceType.xlExternal,
                               SourceData:=lSql,
                               TableDestination:=m_ActiveCell,
                               TableName:=m_OptionsData.PivotName,
                               BackgroundQuery:=False,

An pop-up appears where I should select the database (even the path for DB file was specified in the connection string) and when I select the DB file, it says:
Unrecognized database format 'c:\......\PivotData.mdb'

3. Set-up the connection through the PivotCache
VB
lPivotCache = Globals.ThisWorkbook.PivotCaches.Create(XlPivotTableSourceType.xlExternal)
lPivotCache.Connection = lConStr
lPivotCache.MaintainConnection = True
lPivotCache.CommandText = "SELECT * FROM " & CDatabaseUtils.SAP_PIVOT
lPivotCache.CommandType = XlCmdType.xlCmdSql
lPivotTable = lPivotCache.CreatePivotTable(TableDestination:=m_ActiveCell, TableName:=m_OptionsData.PivotName)

An exception is raised at the command
VB
lPivotCache.MaintainConnection = True

The exception is {"The parameter is incorrect. (Exception from HRESULT: 0x80070057 (E_INVALIDARG))"}.

Any help is much appreciated as I am getting nightmares due to this issue already:)
The application is MS Excel add-in written in VB .NET targetted on .NET Framework 4.0

Tomas
Posted
Updated 14-Nov-13 0:52am
v2
Comments
ZurdoDev 14-Nov-13 9:16am    
Just record a macro in Excel doing what you want.
Maciej Los 17-Nov-13 10:37am    
I would suggest you to use ADO.Recordset and Range.CopyFromRecordset Excel method to copy data from MS Access to Excel. I can provide more details, if you add more details about MS Excel version, sample data, expected output, etc. Why do you want to pivot data using MS Excel?

By The Way: question upvoted!
Meluzin 18-Nov-13 3:00am    
Thank you your help.
Firstly, let me answer your question why do I want use pivot data in MS Excel... It is simply what the application suppose to do - the application is an MS excel Add-in and should be able to create the pivot table on the data which is read from backend system. So the process is following:
* firstly data is read from backend system (based on user's selection)
* at runtime, I create the MS Access database and store all data there (since it can be a lot data records)
* and then I try to create the pivot table on this database

The application is actually an upport from old VB6 project where this procedure worked. I did not come up with better process so I tried to simply upport it.
Maybe the problems are due to the old DAO technology.

I would really appreciate if you could point me or provide some examples with the ADO.Recordset on how it works with MS Sccess DB and what do I need to use it? Mainly if I need to install some additional library or anything else. (I never worked with ADO before.)
The application is MS Excel 2010 add-in on .NET Framework 4.0.
If you want concrete example, I can send you the database on which I need to create the pivot.
Thank you for any help in avdance,
Tomas
thatraja 21-Nov-13 7:59am    
Nicely created question, 5!

Meluzin (Tomas) wrote:
(...) the application is an MS excel Add-in and should be able to create the pivot table on the data which is read from backend system. So the process is following:
* firstly data is read from backend system (based on user's selection)
* at runtime, I create the MS Access database and store all data there (since it can be a lot data records)
* and then I try to create the pivot table on this database


In my opinion it's bad design, sorry...
Why to store data using MS Access, if it is possible to read data direct from any data source (MS Excel, Access, Text Files /e.x. comma-separated/, MS SQL server, XML, etc.). ADO.NET provides functionality to edit, write and delete that data too.
I would suggest to manipulate data using ADO.NET providers instead MS Excel methods.


Have a look:
ADO.NET Overview[^] - please, read related articles
How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET[^]
Walkthrough: Editing an Access Database with ADO.NET[^]
Much ADO About Text Files[^]
Advanced Data Access with ADO.NET and Oracle[^]
IRange.CopyFromRecordset[^]
Programming Pivot Tables for Access Forms[^]
TRANSFORM Statement (Microsoft Access SQL)[^]

Some of related links are not suitable for ADO, but i would like to show you how to create pivots using different data sources.
 
Share this answer
 
Comments
Meluzin 20-Nov-13 4:50am    
Thanks for all the information, I am currently still working on the solution/workaround for this, also with ADO. I made a progress, but still not done yet.

Just one remark to the "bad desin" as you mentioned. All the data is stored in an oracle DB far far far away in the back-end system. Furthermore, during the reading, the data goes through business logic of an underlying consolidation system. Therefore, the call to the back-end system for data is quite expensive, so it should happen only once when user is creating the pivot definition. Once the data is read, it has to be temporary stored somewhere for the pivot. And since it can be huge amout of data, Access DB seems to be better that simple text file. So I would rather stick to this design and use Access DB as the temporary storage. Or do you still think that simple text file is better even for big data volumes?

Nevertheless, I have found out that the original problem is caused by the wrong Access database - when I tried to use another Access DB (created manually) for the pivot, then the pivot table was correctly created. The DB is created with DAO and I am now trying to do everything via ADO and ADOX. I will put details in separate post.
Maciej Los 20-Nov-13 14:35pm    
OK, now your issue is more detailed ;)

Can you accept my answer as a solution - formally (green button)?
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)
VB
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)

' Create the table in the database
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:
VB
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)
...
'Fill the data 
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:
VB
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:
VB
Dim lDatase As Database
Dim lDBEngine As New DBEngine()
lDatase = lDBEngine.CreateDatabase(PivotFilePath, LanguageConstants.dbLangGeneral)

' Create the table in the database
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))

' Append the new TableDef object to the SAPTemp database.
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.
 
Share this answer
 
v4

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900