How to create pivot table with external source - MS Access database

Question

4.78/5 (4 votes)

See more:

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 14-Nov-13 0:09am

Meluzin

Updated 14-Nov-13 0:52am

v2

Add a Solution

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!

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2013-11-18T08:24:00

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.

Meluzin · Accepted Answer · 2013-11-20T23:25:00

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.

How to create pivot table with external source - MS Access database

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0