如何使用外部源创建数据透视表 - MS Access数据库 [英] How to create pivot table with external source - MS Access database
问题描述
您好专家，
我正在努力解决如何使用从外部源（现有MS Access数据库）获取的数据创建数据透视表的问题。任何问题都可以在MS Access中打开数据库，并且数据可以在那里找到。
我发现了3种类似的方法来实现它，但它们都没有工作对我来说：
1.创建记录集并将其添加到PivotCache：
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:
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)
正确创建了与数据库和记录集的连接（我在调试器中验证了记录集确实包含数据库文件中的数据）。但是仍然有一个例外，我尝试将记录集分配给pivotcache：
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:
lPivotCache.Recordset = lRecordSet
异常是：{HRESULT异常：0x800A03EC}
2.使用数据透视表方法
The exception is: {"Exception from HRESULT: 0x800A03EC"}
2. Use the PivotTableWizard method
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,
出现弹出窗口，我应该选择数据库（甚至连接字符串中指定了DB文件的路径），当我选择数据库文件时，它会显示：
无法识别的数据库格式'c：\ ...... \ _PivotData.mdb'
3.通过PivotCache设置连接
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
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
lPivotCache.MaintainConnection = True
例外是{参数不正确。（HRESULT异常：0x80070057（E_INVALIDARG））}。
任何帮助都是非常感谢因为我已经因为这个问题而做噩梦:)
应用程序是用.NET Framework 4.0编写的用于.NET .NET 4.0的MS Excel加载项
Tomas
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
推荐答案
（...）该应用程序是一个MS excel外接程序，应该能够在从后端系统读取的数据上创建数据透视表。所以过程如下：
*首先从后端系统读取数据（根据用户的选择）
*在运行时，我创建MS Access数据库并存储所有数据那里（因为它可以是很多数据记录）
*然后我尝试在这个数据库上创建数据透视表
(...) 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
在我看来这是糟糕的设计，对不起......
如果可以直接从任何数据源读取数据，为什么要使用MS Access存储数据（MS Excel，Access，Text Files / ex逗号分隔/，MS SQL服务器，XML等）。 ADO.NET还提供编辑，写入和删除数据的功能。
我建议使用ADO.NET提供程序来操作数据而不是MS Excel方法。
有一个看：
ADO.NET概述 [ ^ ] - 请阅读相关文章
如何使用ADO.NET检索和修改记录使用Visual Basic .NET的Excel工作簿 [ ^ ]
演练：使用ADO.NET编辑Access数据库 [< a href =http://msdn.microsoft.com/en-us/library/ms971485.aspxtarget =_ blanktitle =New Window> ^ ]
关于文本文件的大量ADO [< a href =http://msdn.microsoft.com/en-us/library/ms974559.aspxtarget =_ blanktitle =新窗口> ^ ]
使用ADO.NET和Oracle进行高级数据访问 [ ^ ]
IRange.CopyFromRecordset [ ^ ]
为访问表单编写数据透视表 [< a href =http://msdn.microsoft.com/en-us/library/office/aa662945%28v=office.11%29.aspxtarg et =_ blanktitle =New Window> ^ ]
TRANSFORM语句（Microsoft Access SQL） [ ^ ]
部分相关链接不适用对于ADO，但我想向您展示如何使用不同的数据源创建枢轴。
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.
最后，将代码重写为ADO帮助。
这是ADO编码。
首先，创建Access DB。这有一个问题 - 简单的ADO无法做到这一点，因此，您必须使用额外的扩展组件ADOX（Microsoft ADO Ext.xx for DDL and Security） - 更多详细信息（这里）
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)
' 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:
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:
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)
这完美无缺。但是如果Access DB是通过带有ODBC连接的DAO创建的，那么数据库会以某种方式损坏，并且无法创建数据透视表（存在错误无法识别的数据库格式）。
DAO用于创建Access DB的编码如下：
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)
' 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()
我不知道为什么这不起作用。如果您有任何提示，我将不胜感激。
我宁愿使用DAO而不是ADO。原因是要使用ADO创建数据库，我还必须使用额外的扩展COM库Microsoft ADO Ext.6.0 for DLL and Security。我想避免它，只在解决方案中使用原生.NET。
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.
这篇关于如何使用外部源创建数据透视表 - MS Access数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
